CODEWITHSUNDEEP
t-sql
Appelman
Appelman

Reputation: 126

Left join filtering only works on where clause

Can someone explain this to me?

I have two huge tables on which I wish to LEFT JOIN, but I think it would be more efficient to filter at the 'on' rather than the 'where'.

Select * from Table1 t1 left join Table2 t2 on t1.Id = t2.Id and t1.Enabled = 1

Rather than

Select * from Table1 t1 left join Table2 t2 on t1.Id = t2.Id where t1.Enabled = 1

The above results are not the same. No matter what I put in as filtering, it stays unaffected:

Select * from Table1 t1 left join Table2 t2 on t1.Id = t2.Id and t1.Enabled = 1

yields exactly the same results as

Select * from Table1 t1 left join Table2 t2 on t1.Id = t2.Id and t1.Enabled = 0

Also, Table1 might only have a few records where Enabled = 1, so it would be inefficient to join millions of records first and then filter to find only 10.

I can do a sub select first, but I somehow I feel it is not the right way:

Select * from (Select * from Table1 where Enabled = 1) a left join Table2 t2 on a.Id = t2.Id

Upvotes: 0

Views: 48

Answers (1)

paparazzo
paparazzo

Reputation: 45096

Types of Joins

Left outer join All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear.

When you move the condition to the where then t1.Enabled = 1 is applied

Do you have actual performance problems with the first query?

Upvotes: 1

Related Questions