Difference between ON and WHERE clauses in SQL table joins

Question

select e.last_name, e.hire_date
from employees e join employees m
on (m.last_name = 'Davies')
and (e.hire_date > m.hire_date);

select e.last_name, e.hire_date
from employees e join employees m
on (m.last_name = 'Davies')
where (e.hire_date > m.hire_date);

select e.last_name, e.hire_date
from employees e join employees m
on (e.hire_date > m.hire_date)
where (m.last_name = 'Davies');

These three statements have the same result. Apart from the fact that where cannot be used exclusively, without using on, is there any particular reason to use where at all in table joins?

Answer 1

The main difference is when you are using different joins.

Typically you should see the same result if you were to use inner joins, but once you start using LEFT joins the results will change.

Have a look at the following example

SQL Fiddle DEMO

And have a look at the following article (very explanatory)

EDIT for @ShannonSeverance

Schema and Test data

CREATE TABLE Table1 (
  ID INT,
  Val VARCHAR(20)
 );

INSERT INTO Table1 VALUES (1,'a');
INSERT INTO Table1 VALUES (2,'a');

CREATE TABLE Table2 (
  ID INT,
  Val VARCHAR(20)
 );

INSERT INTO Table2 VALUES (1,'a');

and Tests

SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.ID = t2.ID AND t1.Val = t2.Val;

SELECT  t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1,Table2 t2 
WHERE t1.ID = t2.ID
 AND t1.Val = t2.Val;

SELECT  t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID  AND t1.Val = t2.Val;

SELECT  t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID  
WHERE t1.Val = t2.Val;

Answer 2

where is a filter which is applied after rows are selected using the join. It is not always the case that a join ... on condition is sematically equivalent to a where condition. Therefore, yes, there is a particular reason to use a where in table joins: when it does the right thing.

---

...and by contrast, the ON condition executes as the join is being made. ON conditions for joins earlier in multi-table joins can cut off millions of unnecessary joins so are generally preferred if semantically correct
– Bohemian

Answer 3

Using on usually used for querying more than one table. When making that query, tables must have relationship each other, in general the same value in a specific fields.

on will connect that same value, for example:

**table1**:

id_name   id_position   name
1         1             john
2         2             doe
3         2             tom
4         3             hawkins

**table2**
id_position   position
1             system analyst
2             programmer

SELECT t1.id_name, t1.name, t2.position
  FROM table1 t1 LEFT JOIN table2 t2
  ON t1.id_position = t2.id_position

-- RESULT:
id_name   name     position
1         john     system analyst
2         doe      programmer
3         tom      programmer
4         hawkins  NULL            -- NO MATCH IN table 2

as we can see on will connect table1 and table2 that have same value id_position, so it is a little different from what you have been written above.

While where can be used in every query and not depends how many tables in that query. In general where is used for conditional thing that we want.