Where vs AND in LEFT JOIN

advertisements

This question already has an answer here:

  • Difference between “on .. and” and “on .. where” in SQL Left Join? 5 answers

I normally don't use an AND in the same line as ON when performing a LEFT JOIN, as I have faced issues in the past. I rather prefer to not go into this pickle and instead put any additional condition in a WHERE clause, which is reliable. But today, out of curiousity, I would love to put this question forward and be clear once and for all.

Question: What really goes on when in a LEFT JOIN when I use the "exta" conditions? Why doesn't it behave in the same manner as WHERE?

SAMPLE QUERIES

create table #a
(
id int,
name varchar(3)
)

create table #b
(
id int,
name varchar(3)
)

insert into #a
select 1, 'abc'
union
select 2, 'def'
union
select 3, 'ghi'

insert into #b
select 1, 'abc'
union
select 2, 'def'

select * from #a a left join #b b on a.id = b.id
where a.id = 3

select * from #a a left join #b b on a.id = b.id
and a.id = 3


This version filters on a.id:

select *
from #a a left join
     #b b
     on a.id = b.id
where a.id = 3

This version does not filter on a.id:

select *
from #a a left join
     #b b
     on a.id = b.id and a.id = 3;

Why not? Go to the definition of the left join. It takes all rows from the first table, regardless of whether the on clause evaluates to true, false, or NULL. So, filters on the first table have no impact in a left join.

Filters on the first table should be in the where clause. Filters on the second table should be in the on clause.