|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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
Show quote
"Meelis Lilbok" <meelis.lil***@deltmar.ee> wrote in message Best bet, run this in query analyzer and look at the execution plans. The 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 > optimizer may end up doing the same thing in either case. If not, then you can see which is faster and why. 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 Show quote "Meelis Lilbok" <meelis.lil***@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" <u***@iscar.co.il> wrote in message Hmm, good point I didn't even actually look at what he was trying to do 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 there. :-) Well I'm pretty sure the syntax error will "return" faster than the left join, so I guess the subselect is technically faster. :-) Show quote > SQL Server DBA Consulting> > > > "Meelis Lilbok" <meelis.lil***@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 (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 Show quote "Uri Dimant" <u***@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.lil***@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 Show quote "Meelis Lilbok" <meelis.lil***@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" <u***@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.lil***@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 Show quote "Uri Dimant" <u***@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.lil***@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" <u***@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.lil***@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 ;-) Show quote "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" <u***@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.lil***@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: Show quote > > 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" <u***@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.lil***@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 > >> > > > >  |
|||||||||||||||||||||||
Other interesting topics