Which SQL query is faster? Filter on Join criteria or Where clause?
For inner joins it doesn't matter where you put your criteria. The SQL compiler will transform both into an execution plan in which the filtering occurs below the join (ie. as if the filter expressions appears is in the join condition).
Outer joins are a different matter, since the place of the filter changes the semantics of the query.
Performance-wise, they are the same (and produce the same plans)
Logically, you should make the operation that still has sense if you replace INNER JOIN
with a LEFT JOIN
.
In your very case this will look like this:
SELECT *
FROM TableA a
LEFT JOIN
TableXRef x
ON x.TableAID = a.ID
AND a.ID = 1
LEFT JOIN
TableB b
ON x.TableBID = b.ID
or this:
SELECT *
FROM TableA a
LEFT JOIN
TableXRef x
ON x.TableAID = a.ID
LEFT JOIN
TableB b
ON b.id = x.TableBID
WHERE a.id = 1
The former query will not return any actual matches for a.id
other than 1
, so the latter syntax (with WHERE
) is logically more consistent.