1

I'm trying to filter out a table using filter in the outer join clause rather than a where clause. When i try to do so i'm getting unexpected results. The whole table is returned as though i didn't apply the filter at all.

When I run this example, i get different results for the last two queries. I would expect them to have the same results but it's not the case. What is going on here?

declare @a table
(
    id int
    ,content varchar(100)
)
declare @b table
(
    id int
    ,content varchar(100)
)

insert into @a (id,content) values (1,'Apple')
insert into @a (id,content) values (2,'Banana')
insert into @a (id,content) values (3,'Orange')
insert into @b (id,content) values (1,'Juice')
insert into @b (id,content) values (2,'Peel')
insert into @b (id,content) values (3,'Julius')

--basic outer join
select * from @a a left join @b b on a.id=b.id

--outer join with where clause filter
select * from @a a left join @b b on a.id=b.id where a.id=1

--outer join with join clause filter
select * from @a a left join @b b on a.id=1 and a.id=b.id
FistOfFury
  • 6,735
  • 7
  • 49
  • 57
  • 1
    Have a look here http://stackoverflow.com/questions/10297231/where-clause-vs-on-when-using-join/10297312#10297312 – StuartLC Jul 17 '12 at 15:27

1 Answers1

4

An outer join is allowed to return NULL for the joined table's row, whereas a where clause must be matched before the result is returned.

select * from @a a left join @b b on a.id=b.id where a.id=1

translates to "Give me all rows from a where id=1 and try to correlate this with any rows in b where a.id=b.id.

select * from @a a left join @b b on a.id=1 and a.id=b.id

on the other hand, translates to "Give me all rows from a and, if a.id=1, try to correlate this with any rows in b where a.id=b.id (otherwise just give me the data from a).

Contrast this with an inner join, where adding a condition to the ON clause and adding it to the WHERE clause is synonymous.

lc.
  • 113,939
  • 20
  • 158
  • 187
  • so if i understand this right, the filter in the join clause is limiting the result set from one of the tables but not both. whereas the where clause filters both. – FistOfFury Jul 18 '12 at 18:11