Is one query always better than the other? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Is one query always better than the other?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-13 : 17:55:52

There's a JOIN I do a lot - joining User records to a table of completions. It's always LEFT OUTER because I want to know which users have completed and which have not.

There are two ways to do this, and I wonder if one is always better than the other.

SELECT UserID, CompletionDate

FROM Users U

LEFT OUTER JOIN Completions C ON C.UserID = U.UserID

WHERE CompletionDate IS NULL OR CompletionDate BETWEEN 'Jan 1 2003' AND GETDATE()

The other way would be

SELECT UserID, CompletionDate

FROM Users U

LEFT OUTER JOIN (

Select UserID, CompletionDate

FROM Completions

WHERE CompletionDate BETWEEN 'Jan 1 2003' AND GETDATE()

) C ON C.UserID = U.UserID

While the 2nd format seems intuitively better to me because it eliminates records prior to the join, the SQL optimizer usually makes queries like the two above equivalent. The execution plan for the 2nd query surprisingly shows a lazy spool, which is usually the KOD*. The next surprise is I timed both and the second query runs faster than the first on my test data even with the lazy spool.

Any discussion appreciated.

Sam

* -- Kiss Of Death

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-13 : 18:03:37

Actually, I prefer:

LEFT OUTER JOIN Completions C ON
C.UserID = U.UserID AND
CompletionDate BETWEEN 'Jan 1 2003' AND GETDATE()

Note that this one, and the two you have posted, return different results! If you intend to basically do what the query above is doing, you never should say:

LEFT OUTER JOIN TableB
ON <join condition>
WHERE <some col from TableB is null> or <some condition on TableB>

Which is what you wrote in your first query.

Why? because, what if there IS a matching row in TableB based on the JOIN condition -- meaning the null check fails -- but it does not satisfy the condition in the second half of the WHERE ? The entire row is eliminated from the results ! But, you wanted an outer join to return ALL rows from the inner table.

In other words, check the results of your two queries when a user has a completed date of < 1/1/2003. The first query will not show the user at ALL, but the second one (and mine) will show the user but with a NULL completion date.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-13 : 18:29:08

Jeff,

I like the query structure you propose and it appears more correct, so I'll use it.

But I don't agree with your analysis of the 1st query. It's common to look for rows where no match is found using the IS NULL condition with a LEFT OUTER JOIN -- IF there are NO NULL values in the RIGHT table. Right? I mean the row won't be eliminated as you pointed out because this isn't an INNER JOIN.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-13 : 18:33:21

Try it out, Sammy !!!


declare @t1 table (id int)

declare @t2 table (id int, dt datetime)

insert into @t1
select 1 union
select 2 union
select 3 union
select 4

insert into @t2
select 1, '1/1/2003' union
select 2, '12/31/2002' union
select 3, '2/1/2003'

select t1.id, t2.dt
from @t1 t1
left outer join
@t2 t2
on t1.id = t2.id and t2.dt between '1/1/2003' and '12/31/2003'

select t1.id, t2.dt
from @t1 t1
left outer join
@t2 t2 on t1.id = t2.id 
where t2.id is null or t2.dt between '1/1/2003' and '12/31/2003'

different results are returned for the reasons I mentioned.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-13 : 18:47:32

Must.... Have.... Coffee.....
Must.... Have.... Coffee.....
Must.... Have.... Coffee.....

I knew that! I just wanted to see if you knew that!

Argh!

You've saved me a big future debugging headache! Thanks !

I'm *still* voting you Dr. Cross Join when 2000 passes.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-13 : 19:53:05

Any time Sam ! it's a common mistake. i myself had tried that approach in the past. It seems like it SHOULD work, until you work it out step by step and realize why it doesn't. but it's not obvious!

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-14 : 07:19:22

quote:
I'm *still* voting you Dr. Cross Join when 2000 passes.

Like we need any proof:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30427

Where there's a will, I want to be in it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-14 : 09:37:14

You guys are too much !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources