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...
>
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...
>
|||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...
>
|||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...
>