June 25, 2009 at 5:41 am
Hi
What is the difference, and which is best between a join filter and a post-join filter. Very simplified :
create table aaa (col1 int, col2 varchar(2))
create table bbb (col1 int, col2 varchar(2))
create table ccc (col1 int, col2 varchar(2))
create clustered index idx_aaa on aaa(col1)
create clustered index idx_bbb on bbb(col1)
create clustered index idx_ccc on ccc(col1)
insert into aaa values (1, 'aa')
insert into aaa values (2, 'bb')
insert into aaa values (3, 'bb')
insert into bbb values (1, 'aa')
insert into bbb values (2, 'bb')
insert into bbb values (3, 'bb')
insert into ccc values (1, 'aa')
insert into ccc values (2, 'bb')
insert into ccc values (3, 'bb')
--Post Join Fitler
select aaa.col1, bbb.col1, ccc.col1
from aaa
inner join bbb on bbb.col1 = aaa.col1
inner join ccc on ccc.col1 = bbb.col1
where bbb.col2 = 'aa'
and ccc.col2 = 'aa'
--Join Filter
select aaa.col1, bbb.col1, ccc.col1
from aaa
inner join bbb on bbb.col1 = aaa.col1 and bbb.col2 = 'aa'
inner join ccc on ccc.col1 = bbb.col1 and ccc.col2 = 'aa'
The same query plan is returned in both instances, so does it matter which one I use? When will the query plan NOT be the same for the 2 different statements?
Thanks
June 25, 2009 at 5:46 am
It's not you to decide, it's the query optimizer. Personally I never join on columns that are not to be joined and I keep filters in the WHERE clause. This allows me to eventually change the join from INNER to OUTER, even if the WHERE clause, not testing NULLS, will turn the JOIN from OUTER to INNER.
Anyway, it doesn't matter where you write the filter predicate, it will always be the query optimizer to decide the best plan to accompilsh the task.
Regards
Gianluca
-- Gianluca Sartori
June 25, 2009 at 6:15 am
Those two queries are not equivalent. In the second one, only rows from bbb or ccc that satisfy the condition are presented for the join. Now, in an inner join, the results are the same, but that's not the case in an outer join.
select aaa.col1, bbb.col1, ccc.col1
from aaa
LEFT OUTER join bbb on bbb.col1 = aaa.col1
LEFT OUTER join ccc on ccc.col1 = bbb.col1
where bbb.col2 = 'aa'
and ccc.col2 = 'aa'
-- returns 1 row
--Join Filter
select aaa.col1, bbb.col1, ccc.col1
from aaa
LEFT OUTER JOIN bbb on bbb.col1 = aaa.col1 and bbb.col2 = 'aa'
LEFT OUTER JOIN ccc on ccc.col1 = bbb.col1 and ccc.col2 = 'aa'
-- returns three rows.
When doing an inner join, the results and exec plan will be the same. When doing an outer, the results will differ.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2009 at 6:26 am
Thanks a lot! This makes perfect sense.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply