December 15, 2010 at 5:14 am
I have two queries. they both return different data, despite looking very similiar. The only difference is that one of the filters has been moved out of the where clause to the join. can anyone advise why the first query works but the 2nd one does not?
insert into po(ponumber,agreementid,potype,usagedate) values('po1',2,'NE',dateadd(mm,-5,getdate()))
insert into po(ponumber,agreementid,potype,usagedate) values('po2',2,'NE',dateadd(mm,-5,getdate()))
insert into po(ponumber,agreementid,potype,usagedate)values('zupo1',2,'ZU',dateadd(mm,-5,getdate()))
insert into po(ponumber,agreementid,potype,usagedate) values('zu3',1,'ZU',dateadd(mm,-5,getdate()))
insert into po(ponumber,agreementid,potype,usagedate) values('zu4',1,'ZU',dateadd(mm,-5,getdate()))
insert into po(ponumber,agreementid,potype,usagedate)values('zu5',1,'ZU',dateadd(mm,-5,getdate()))
insert into ag(agreementnumber) values('Ag1')
insert into ag(agreementnumber) values('Ag2')
insert into ag(agreementnumber) values('Ag3')
insert into ag(agreementnumber) values('Ag4')
insert into ag(agreementnumber) values('Ag5')
select ag.agreementid,po.ponumber,po2.ponumber
from po join ag on po.agreementid = ag.agreementid
left join po po2 on ag.agreementid = po2.agreementid
and po2.potype <> 'ZU' --only difference. this line is in the join rather than the where clause
where po.potype = 'ZU'
and po2.poid is null
select ag.agreementid,po.ponumber,po2.ponumber
from po join ag on po.agreementid = ag.agreementid
left join po po2 on ag.agreementid = po2.agreementid
where po.potype = 'ZU'
and po2.poid is null
and po2.potype <> 'ZU'
December 15, 2010 at 5:38 am
According to my experience,first one is more fast then other .
December 15, 2010 at 5:43 am
Developer 2005 (12/15/2010)
According to my experience,first one is more fast then other.
It's not a question of speed. They are two distinct, different queries, they are asking different things and may return different results.
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
December 15, 2010 at 5:46 am
winston Smith (12/15/2010)
I have two queries. they both return different data, despite looking very similiar. The only difference is that one of the filters has been moved out of the where clause to the join. can anyone advise why the first query works but the 2nd one does not?
They both work, but they are not asking for the same thing. They are distinctly different queries.
With the filter in the join, you're filtering the second table before the join. With the filter in the where, you're filtering after the join. If the join was an inner join, that would be equivalent. Since it's an outer, the results can be different between the two.
http://sqlskills.com/BLOGS/KIMBERLY/post/Determining-the-position-of-search-arguments-in-a-join.aspx
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
December 15, 2010 at 6:43 am
winston Smith (12/15/2010)
I have two queries. they both return different data, despite looking very similiar. The only difference is that one of the filters has been moved out of the where clause to the join. can anyone advise why the first query works but the 2nd one does not?
Here's a slight variation on your query so that query 2 generates output (the same output):
DROP TABLE #po
DROP TABLE #ag
CREATE TABLE #ag (agreementid INT IDENTITY (1,1), agreementnumber CHAR(3))
CREATE TABLE #po (poid INT, ponumber VARCHAR(5), agreementid INT, potype CHAR(2), usagedate DATETIME)
insert into #po(poid, ponumber,agreementid,potype,usagedate) values(1, 'po1',2,'NE',dateadd(mm,-5,getdate()))
insert into #po(poid, ponumber,agreementid,potype,usagedate) values(2, 'po2',2,'NE',dateadd(mm,-5,getdate()))
insert into #po(poid, ponumber,agreementid,potype,usagedate)values(3, 'zupo1',2,'ZU',dateadd(mm,-5,getdate()))
insert into #po(poid, ponumber,agreementid,potype,usagedate) values(4, 'zu3',1,'ZU',dateadd(mm,-5,getdate()))
insert into #po(poid, ponumber,agreementid,potype,usagedate) values(5, 'zu4',1,'ZU',dateadd(mm,-5,getdate()))
insert into #po(poid, ponumber,agreementid,potype,usagedate)values(NULL,'zu5',1,'ZU',dateadd(mm,-5,getdate()))
insert into #ag(agreementnumber) values('Ag1')
insert into #ag(agreementnumber) values('Ag2')
insert into #ag(agreementnumber) values('Ag3')
insert into #ag(agreementnumber) values('Ag4')
insert into #ag(agreementnumber) values('Ag5')
select ag.agreementid,po.ponumber,po2.ponumber
from #po po
join #ag ag on po.agreementid = ag.agreementid
left join #po po2 on ag.agreementid = po2.agreementid
and po2.potype = 'ZU' --only difference. this line is in the join rather than the where clause
where po.potype = 'ZU'
and po2.poid is null
select ag.agreementid,po.ponumber,po2.ponumber
from #po po
join #ag ag on po.agreementid = ag.agreementid
left join #po po2 on ag.agreementid = po2.agreementid
where po.potype = 'ZU'
and po2.poid is null
and po2.potype = 'ZU'
Run the whole script then compare the actual plans.
Query 1 shows a nested loops outer join between po2 and [rest]
Query 2 shows a nested loops INNER JOIN between po2 and po.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2010 at 6:54 am
The answer lies in the question. Filter in JOIN & filter in WHERE clause.
When you add filter in JOIN the rows from respective table will be filtered based on the argument passed in AND clause, which affects the output from next JOIN.
Whereas in next query the filter is used on actual output from the query.
Abhijit - http://abhijitmore.wordpress.com
December 15, 2010 at 7:54 am
winston Smith (12/15/2010)
I have two queries. they both return different data, despite looking very similiar. The only difference is that one of the filters has been moved out of the where clause to the join. can anyone advise why the first query works but the 2nd one does not?
Has already pointed out, the issue is the LEFT OUTER JOIN. However, this hasn't been explained, so lets look into it a bit.
select ag.agreementid,po.ponumber,po2.ponumber
from po join ag on po.agreementid = ag.agreementid
left join po po2 on ag.agreementid = po2.agreementid
and po2.potype <> 'ZU' --only difference. this line is in the join rather than the where clause
where po.potype = 'ZU'
and po2.poid is null
select ag.agreementid,po.ponumber,po2.ponumber
from po join ag on po.agreementid = ag.agreementid
left join po po2 on ag.agreementid = po2.agreementid
where po.potype = 'ZU'
and po2.poid is null
and po2.potype <> 'ZU'
The reason that you are getting a difference is that there is a record in po2 for that agreementid, but it's potype = 'ZU'.
On the LEFT JOIN that has the filter (and potype <> 'ZU'), you are specifically limiting the result set of that table to only have records with potype <> 'ZU' - this excludes the ones where potype = 'ZU'. Since this is a LEFT JOIN, and there isn't a match since it was filtered out here, it assigns NULLS to all of the columns in po2 for that agreementid.
In the other query, you are getting the JOIN match on the agreementid - but then filtering that row out completely because it doesn't match the "po2.potype <> 'ZU'" in the where clause.
So, in the query with the filter in the join, you end up with a record, but the values from the po2 table are all NULL. In the query with the filter in the where, you end up with that record being omitted from the final result set.
The question to you is: do you want to see that record with a NULL, or do you want that record omitted? That will determine which method you should utilize.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply