Difference in select statements

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

Transact-SQL (2000)

Difference in select statements

Author

Topic

zurbum
Yak Posting Veteran

55 Posts

Posted - 2004-03-27 : 11:50:46

I would like to ask if is there any difference between these two SELECTs. The result is the same, but is one faster than the other or which one is better to use.

SELECT somethingA, somethinB FROM table1 LEFT JOIN table2 ON table1.somethingA=table2.somethingA AND table1.active=1

SELECT somethingA, somethinB FROM table1 LEFT JOIN table2 ON table1.somethingA=table2.somethingA WHERE table1.active=1

Thanx.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2004-03-27 : 15:51:44

You'll want to compare the execution plan of the two queries to decide (and run the queries several times to make sure the execution plan is consistently different.) I ran the following two queries against the Northwind database and came back with identical execution plans...

select o.orderid, c.companyname
from orders o inner join customers c
on o.customerid = c.customerid
and country = 'Germany'

select o.orderid, c.companyname
from orders o inner join customers c
on o.customerid = c.customerid
where country = 'Germany'

Justin

"Hit me with a shovel 'cause I can't believe I dug you."

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-27 : 16:15:24

--The best test is always a test.

DECLARE @table1 TABLE(
somethingA VARCHAR(55),
active INT)

DECLARE @table2 TABLE(
somethingA VARCHAR(55),
somethinB VARCHAR(55))

INSERT @table1(
somethingA,
active)

SELECT 'test1',0
UNION ALL
SELECT 'test2',1
UNION ALL
SELECT 'test3',1
UNION ALL
SELECT 'test4',0
UNION ALL
SELECT 'test5',1

INSERT @table2(
somethingA,
somethinB)

SELECT 'test1','test1b'
UNION ALL
SELECT 'test2','test2b'
UNION ALL
SELECT 'test3','test3b'
UNION ALL
SELECT 'test4','test4b'

SELECT t1.somethingA, t2.somethinB FROM @table1 t1 LEFT JOIN @table2 t2 ON t1.somethingA= t2.somethingA AND t1.active=1

SELECT t1.somethingA, t2.somethinB FROM @table1 t1 LEFT JOIN @table2 t2 ON t1.somethingA= t2.somethingA WHERE t1.active=1

Result Set 1:
test1 NULL
test2 test2b
test3 test3b
test4 NULL
test5 NULL

Result Set 2:
test2 test2b
test3 test3b
test5 NULL

Remember that a where clause is processed "before" the joins. This is extremely important when you begin to look at outer joins. The reason this returned different sets was because the table1.active=1 was evaluated AFTER the initial join. It only limited return sets on the right side of the join as opposed to being evaluated before and limiting the entire set.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-03-27 : 18:59:55

quote:
Remember that a where clause is processed "before" the joins.

Typo? I think it should be "after".

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-28 : 01:25:37

MOO
In terms of readability, I'd say option 2 is better. I always try to avoid adding filter conditions as part of the join when the filter is on the significant table (the "LEFT" table). But if the filter is on the "RIGHT" table in a LEFT JOIN, you have no choice but to include it as part of the JOIN condition.

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-28 : 10:33:28

quote:

quote:
--------------------------------------------------------------------------------
Remember that a where clause is processed "before" the joins.
--------------------------------------------------------------------------------

Typo? I think it should be "after".

I think it is fair to say that a join condition that affects only 1 table will be applied before the join ... conditions that affect all tables , or that can't be evaluated until after the joins (i.e., multiple OR conditions), would be after.

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-28 : 12:52:40

If you look at your execution plan, the items in the where clause for the first table of your from statement are always evaluated before the joins. If you have a very selective table that quickly cuts the recordset down, SQL Server should figure that out, but many times it's more efficient to put it as the first table in the join and have the limiting criteria in the where clause.

Try it out. :) Let me know if I'm wrong. I always like good tests.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-03-28 : 20:53:59

Derrick;

Everything is OK so far.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-28 : 21:12:30

What's OK so far?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-03-28 : 23:51:14

Everything.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-31 : 06:03:49

quote:
Originally posted by derrickleggett

If you look at your execution plan, the items in the where clause for the first table of your from statement are always evaluated before the joins. If you have a very selective table that quickly cuts the recordset down, SQL Server should figure that out, but many times it's more efficient to put it as the first table in the join and have the limiting criteria in the where clause.

I agree with Derrick

If you have 2 tables A & B and you want to join the 2 but only want to select a few from A then SQL will filter (with a where) then join the two.

To the original author (Zurbum) - is the second select faster?

You can't teach an old mouse new clicks.

zurbum
Yak Posting Veteran

55 Posts

Posted - 2004-04-04 : 15:49:10

I didn't notice that one is faster than the other... I just wandered which is better, because both gave me same results.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-04 : 16:51:44

You can tell by looking at the execution plan and comparing the costs. The difference in runtime will be bigger as the row counts grow.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources