Question on select execution

Question

Post reply

Question on select execution

babuganesh2000

Hall of Fame

Points: 3123
More actions
April 14, 2006 at 2:39 pm

#117032

Hi Db gurus,
declare @var1 int
select @var1 = 1234
select a.*
from table1 a (nolock)
join  table 2 b (nolock)
  on a.col1 = b.col1
where a.col1 = @var1
I would like to understand which one scans first, either the where condition or the join condition. Say for instance if I change like this will it improve performance.
declare @var1 int
select @var1 = 1234
select a.*
from table1 a (nolock)
join  table 2 b (nolock)
  on a.col1 = @var1
and b.col1 = @var1
Thanks in advance for the explanation 🙂
Thanks,
Ganesh

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110209 More actions · Answer 1

There is no "JOIN" ib SQL 2000.

There are:

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Which one you mean to use?

If you mean "INNER JOIN" (parser will make this replacement automatically) then your queries are identical. No difference in execution.

If you mean something else you queries will probably return different results. No point to compare performance.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Not quite true... JOIN is an acceptable shortcut for INNER JOIN. Additionally, LEFT JOIN, RIGHT JOIN, and FULL JOIN are acceptable shortcuts for the similarly named OUTER counterparts...

USE Northwind

SELECT o.CustomerID,od.*

FROM Orders o

JOIN [Order Details] od

ON od.OrderID = o.OrderID

... and, they are listed in BOL under [Left Outer Join][Using Joins]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110209 More actions · Answer 3

So, there is no JOIN, there is INNER JOIN, and you may use alias name "JOIN" for referensing actual "INNER JOIN".

Same for LEFT JOIN and LEFT OUTER JOIN.

If you do not feel yourself free with SQL Server sysntax better not to cut corners, just not to fool yourself.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

I agree... I always spell them out all the way...

I don't believe BOL calls them "alias names" though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Antares686 SSC Guru Points: 125444 More actions · Answer 5

In your example this join really cannot be improved on by placement in most all cases (this doesn't mean an exception couldn't occurr).