[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Variables & Results: Where vs. Join

Raterus

3/30/2007 1:50:00 PM

Will there EVER be a difference in results returned between using a variable in the join vs. using it in the where clause?

Select *
from mytable a left join anothertable b on a.col = b.col and a.somedate = @somedate

vs.

Select *
from mytable a left join anothertable b on a.col = b.col
Where a.somedate = @somedate
4 Answers

Tom Cooper

3/30/2007 1:58:00 PM

0

Those two queries will always return the same result set. Note, however,
that if instead you compare columns from the table on the right side of the
left outer join, it makes a difference. That is, the following two queries
ARE different (the second one is effectively an inner join).

Select *
from mytable a left join anothertable b on a.col = b.col and b.somedate =
@somedate

vs.

Select *
from mytable a left join anothertable b on a.col = b.col
Where b.somedate = @somedate

Tom

"Raterus" <raterus@hotmail.com> wrote in message
news:e9c8OJtcHHA.4720@TK2MSFTNGP04.phx.gbl...
Will there EVER be a difference in results returned between using a variable
in the join vs. using it in the where clause?

Select *
from mytable a left join anothertable b on a.col = b.col and a.somedate =
@somedate

vs.

Select *
from mytable a left join anothertable b on a.col = b.col
Where a.somedate = @somedate


Pace

3/30/2007 2:00:00 PM

0

The only difference may be the IO stats.

Check the execution plan and see which gives the lowest IO, I should imagine
there is a slightly more overhead on the where, though im not completely
sure. Check it out ;-)

"Raterus" <raterus@hotmail.com> wrote in message
news:e9c8OJtcHHA.4720@TK2MSFTNGP04.phx.gbl...
Will there EVER be a difference in results returned between using a variable
in the join vs. using it in the where clause?

Select *
from mytable a left join anothertable b on a.col = b.col and a.somedate =
@somedate

vs.

Select *
from mytable a left join anothertable b on a.col = b.col
Where a.somedate = @somedate

Steve Dassin

3/30/2007 7:37:00 PM

0


"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:eKwwyNtcHHA.4836@TK2MSFTNGP03.phx.gbl...
> Note, however, that if instead you compare columns from the table on the
> right side of the left outer join, it makes a difference.

> Select *
> from mytable a left join anothertable b on a.col = b.col and b.somedate =
> @somedate

This is a pure form of sql injection. Ask Hugo -:)


--CELKO--

3/31/2007 12:35:00 AM

0

yes. Google some of my old postings on how ANSI LEFT OUTER JOIN
works. The WHERE is applied after the FROM clause builds the working
table.