SQL join: where clause vs. on clause

Question

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different.

---

What is the difference and what should go in each?

If I understand the theory correctly, the query optimizer should be able to use both interchangeably.

Answer 1

In SQL, the 'WHERE' and 'ON' clause,are kind of Conditional Statemants, but the major difference between them are, the 'Where' Clause is used in Select/Update Statements for specifying the Conditions, whereas the 'ON' Clause is used in Joins, where it verifies or checks if the Records are Matched in the target and source tables, before the Tables are Joined

For Example: - 'WHERE'

SELECT * FROM employee WHERE employee_id=101

For Example: - 'ON'

*There are two tables employee and employee_details, the matching columns are employee_id.*

SELECT * FROM employee INNER JOIN employee_details ON employee.employee_id=employee_details.employee_id

Hope I have answered your Question.Revert back for clarifications.

Answer 2

On INNER JOINs they are interchangeable, and the optimizer will rearrange them at will.

On OUTER JOINs, they are not necessarily interchangeable, depending on which side of the join they depend on.

I put them in either place depending on the readability.

Answer 3

for better performance tables should have a special indexed column to use for JOINS .

so if the column you condition on is not one of those indexed columns then i suspect it is better to keep it in WHERE .

so you JOIN using the indexed columns, then after JOIN you run the condition on the none indexed column .

Answer 4

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Answer 5

For an inner join, WHERE and ON can be used interchangeably. In fact, it's possible to use ON in a correlated subquery. For example:

update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)

This is (IMHO) utterly confusing to a human, and it's very easy to forget to link table1 to anything (because the "driver" table doesn't have an "on" clause), but it's legal.

Answer 6

There is great difference between where clause vs. on clause, when it comes to left join.

Here is example:

mysql> desc t1; 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fid   | int(11)     | NO   |     | NULL    |       |
| v     | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

There fid is id of table t2.

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| v     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Query on "on clause" :

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' 
    -> ;
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  1 |   1 | H | NULL | NULL |
|  2 |   1 | B | NULL | NULL |
|  3 |   2 | H | NULL | NULL |
|  4 |   7 | K | NULL | NULL |
|  5 |   5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)

Query on "where clause":

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  4 |   7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)

It is clear that, the first query returns a record from t1 and its dependent row from t2, if any, for row t1.v = 'K'.

The second query returns rows from t1, but only for t1.v = 'K' will have any associated row with it.