Must be about a month ago now, myself and James were trying to work out the difference between
select * from a inner join b on a.x = b.y and a.z = az
and
select * from a inner join b on a.x = b.y where a.z = az
Thing is we couldn't find a difference and thought it may even assist in the sql planning since the predicates are placed as soon as they can be which should hopefully mean sql plans the filters sooner. However it never seemed to pan out.
Today however I've found a difference, the join filter is applied before the outer join and in where the data was instead of eliminating a row, I got an outer join on nulls.
declare @a table (data varchar(30), x int)
declare @b table (x int, data varchar(30) null)
insert into @a values ('hello',1)insert into @b values (1,'world')
select a.data, b.data from @a a left outer join @b b on a.x = b.x and b.data != 'world'
select a.data, b.data from @a a left outer join @b b on a.x = b.x where b.data != 'world'
It gets better, should you apply a join hint to the query there is NO visible difference in the query plans shown
select a.data, b.data from @a a left outer hash join @b b on a.x = b.x and b.data != 'world'
select a.data, b.data from @a a left outer join @b b on a.x = b.x where b.data != 'world'
