faster?

Hi
what is faster - LEFT JOIN or subselect
example
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>
Best bet, run this in query analyzer and look at the execution plans. The
optimizer may end up doing the same thing in either case. If not, then you
can see which is faster and why.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Meelis
This is invalid in terms of syntax statement. Post the valid statements and
regarding to the question LEFT JOIN has nothing to do with SUBSELECT
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
Hmm, good point I didn't even actually look at what he was trying to do
there. :-)
Well I'm pretty sure the syntax error will "return" faster than the left
join, so I guess the subselect is technically faster. :-)
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
FROM TITLES T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>|||Meekis
Again , these queries gave different results
use Nortwind
go
SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
ON c.Customerid=o.Customerid
SELECT o.orderid,(SELECT customerid FROM Customers WHERE
o.Customerid=Customerid)
FROM Orders o
Also there are diffrerences in execution plan show that the LEFT JOIN
performed better
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>> Meelis
>> This is invalid in terms of syntax statement. Post the valid statements
>> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>>
>>
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON
>> T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>>
>|||hmm
thats because orders MUST have CustomerID
but in my sample
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
SINDEXES MAY have TITLE_ID
Meelis
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvCWU$yWHHA.4240@.TK2MSFTNGP06.phx.gbl...
> Meekis
> Again , these queries gave different results
> use Nortwind
> go
> SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
> ON c.Customerid=o.Customerid
> SELECT o.orderid,(SELECT customerid FROM Customers WHERE
> o.Customerid=Customerid)
> FROM Orders o
>
> Also there are diffrerences in execution plan show that the LEFT JOIN
> performed better
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>> FROM TITLES T
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>> Meelis
>> This is invalid in terms of syntax statement. Post the valid statements
>> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>>
>>
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON
>> T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>>
>>
>|||...I'd say from the face of it the first option would be faster, the second
would be running a correlated subquery for each row returned... ...but as
Greg said check the execution plan, these things can vary based on the number
of rows returned, available indexes, etc - don't underestimate the optimizer
;-)
"Meelis Lilbok" wrote:
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> > Meelis
> >
> > This is invalid in terms of syntax statement. Post the valid statements
> > and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
> >
> >
> >
> >
> > "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> > news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> what is faster - LEFT JOIN or subselect
> >>
> >>
> >> example
> >>
> >> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> >> or
> >>
> >> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> >>
> >>
> >>
> >> Mex
> >>
> >
> >
>
>|||As other have posted, SQL Server will most likely select the fastest
query plan itself, whether you have used syntax 1 or 2.
Whether loop join, merge join or hash join is fastest will depend on the
index depth, relative table size and data distribution. Of course, this
all assumes that both tables are properly indexed.
Gert-Jan
Meelis Lilbok wrote:
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> > Meelis
> >
> > This is invalid in terms of syntax statement. Post the valid statements
> > and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
> >
> >
> >
> >
> > "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> > news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> what is faster - LEFT JOIN or subselect
> >>
> >>
> >> example
> >>
> >> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> >> or
> >>
> >> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> >>
> >>
> >>
> >> Mex
> >>
> >
> >