principleMinor
placing conditions in a join query join on vs where
Viewed 0 times
placingquerywherejoinconditions
Problem
Is there and advantage to one of the following options over the other or are they the same ?
vs.
And in general - are there performance (dis)advantages to placing the conditions within the JOIN ON statement as opposed to the WHERE statement ?
select *
from T1
Join T2
on T2.id=T1.t2_id
and T2.field=value;vs.
select *
from T1
Join T2
on T2.id=T1.t2_id
where T2.field=value;And in general - are there performance (dis)advantages to placing the conditions within the JOIN ON statement as opposed to the WHERE statement ?
Solution
When using inner joins there is no semantic difference between the two, and it would usually be make sense to put join related conditions in the
However, when using outer joins the semantics are not the same.
for example: in a left join - rows from the left table will be returned even if there the
So for
Note that in some cases this is actually desirable.
e.g. - In Queries such as:
JOIN and filtering ones in the WHERE clause.However, when using outer joins the semantics are not the same.
for example: in a left join - rows from the left table will be returned even if there the
JOIN ON includes some limitation on the fields from right table that are not met. but if the same limitation would be moved to the WHERE clause - the (already Left Joined) row from the left table that has no matching row on the right table will be discarded.So for
INNER JOIN - it does not matter - for OUTER JOIN - you should think what exactly do you want to get.Note that in some cases this is actually desirable.
e.g. - In Queries such as:
SELECT *
FROM T1 LEFT JOIN T2 ON T2.id = T1.t2_id
WHERE T2.id IS NULL ;Code Snippets
SELECT *
FROM T1 LEFT JOIN T2 ON T2.id = T1.t2_id
WHERE T2.id IS NULL ;Context
StackExchange Database Administrators Q#95721, answer score: 4
Revisions (0)
No revisions yet.