DB Query Planners are (usually) smart and will rewrite the query to make it the most efficient. BUT, there is still a difference between the two queries (especially if we ignore the planner intervention).
For the example in the question, it makes no difference. If you use
OUTER JOINs, it does. If you add extra conditions, it also makes a difference. And if you have several joins, it can have a great impact, both in terms of results and efficiency.
When the filtering condition is put inside the
JOIN, it is evaluated right away and only rows satisfying the condition are used in the next join.
When the filtering condition is put in the
WHERE clause, it is evaluated at the end, so all rows are considered.
WHERE a.id = b.id AND b.id = c.id
AND a.val=1 AND b.val=2 AND c.val=3;
--> do a
CROSS JOIN between a,b,c. From all rows (so a size * b size * c size), keep the ones satisfying the condition.
JOIN B ON a.id = b.id AND a.val = 1 AND b.val = 2
JOIN C ON b.id = c.id AND c.val = 3;
--> get all rows from A. Keep the rows having
a.val=1. Match rows in B by
id and keep only the rows having
b.val=2. Using this partial result set, match rows in C by
id and keep the rows having
LEFT JOIN, the difference is in the result. Suppose we have an entry in table A with no match (by ID) in table B.
LEFT JOIN B ON a.id = b.id
WHERE b.val =2;
--> the row that exists only in A is kept in the join. The
WHERE clause filters it out.
LEFT JOIN B ON a.id = b.id AND b.val = 2;
--> There is no row in B matching the row ID and
b.val, so the right side of the join is NULL. Since there is a row on the left side of the join, the row is returned (
A.* is populated,
B.* is null)