HiveBrain v1.2.0
Get Started
← Back to all entries
principleMinor

placing conditions in a join query join on vs where

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
placingquerywherejoinconditions

Problem

Is there and advantage to one of the following options over the other or are they the same ?

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 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.