CODEWITHSUNDEEP
sqloracle-databasejoinfilter
adjrock
adjrock

Reputation: 11

SQL Filtering when joining

bit of a novice question, I am running a query and left joining and wanted to know whether there was a difference when you specify a filter in terms of performance, in e.g below, top I filter straight after first join and below I do all joins and then filter:

Select t1.*,t2.* from t1 t1    
left join t2 t2    
   on t1.key = t2.key    
   and t1.date < today    
left join t3 t3 
   on t2.key2 = t3.key

vs

Select t1.*,t2.*  from t1 t1    
left join t2 t2    
     on t1.key = t2.key       
left join t3 t3
     on t2.key2 = t3.key    
     and t1.date < today

Upvotes: 0

Views: 63

Answers (1)

philipxy
philipxy

Reputation: 15118

Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN.

In general your queries have different inner join & null-extended rows for the 1st left joins & then further differences due to more joining. Unless certain constraints hold, the 2 queries return different functions of their inputs. So comparing their performance seems moot.

Upvotes: 1

Related Questions