Join performance

[Xmen Real]

I'm a bit confuse in following comparision

select * from table1 t1 join table2 t2 on t1.ID=t2.ID where t1.ID>5

vs

select * from (select * from table1 where ID>5) t1 join table2 t2 on t1.ID=t2.ID

the second query make sense that it will be faster as its filtering rows before join but I wanna know that does 'where' executes before 'join' (in first query condition) ?

TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L %^]*IRXD#@GKCQ`R\^SF_WcHbORY87֦ʻ6ϣN8ȤBcRAV\Z^&SU~%CSWQ@#2 W_AD`EPABIKRDFVS)EVLQK)JKQUFK[M`UKs*$GwU#QDXBER@CBN% R0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i’TV.C\y<pŠjxsg-b$f4ia>

----------------------------------------------- 128 bit encrypted signature, crack if you can

[Mycroft Holmes]

Try turning on the execution plan and examine the details. I doubt it makes any different at all as SQL interprets the query anyway. Don't forget you can also have

Select *
From Table1, Table2
where Table1.something = 'value'
and table1.key = table2.key

I'm pretty sure the execution plan is the same but using the join style makes it more readable and therefore easier to support.

Never underestimate the power of human stupidity RAH

[Vimalsoft Pty Ltd]

Good Morning Xmen W.K. 1)Select only the Column you want to display 2)The Second Query is a bad Query 3)Do you have indexes on the ID Field ?

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Xmen Real]

why select only one column if I need all of 'em...I know in that case ID column will appear twice as it will from both tables but its just an example... and why second is bad ?

TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L %^]*IRXD#@GKCQ`R\^SF_WcHbORY87֦ʻ6ϣN8ȤBcRAV\Z^&SU~%CSWQ@#2 W_AD`EPABIKRDFVS)EVLQK)JKQUFK[M`UKs*$GwU#QDXBER@CBN% R0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i’TV.C\y<pŠjxsg-b$f4ia>

----------------------------------------------- 128 bit encrypted signature, crack if you can

[Vimalsoft Pty Ltd]

Xmen i was wrong i misjudged your statements. The Queries are the same and the execution plan is the same in all parts.

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Xmen Real]

no problem ;)

TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L %^]*IRXD#@GKCQ`R\^SF_WcHbORY87֦ʻ6ϣN8ȤBcRAV\Z^&SU~%CSWQ@#2 W_AD`EPABIKRDFVS)EVLQK)JKQUFK[M`UKs*$GwU#QDXBER@CBN% R0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i’TV.C\y<pŠjxsg-b$f4ia>

----------------------------------------------- 128 bit encrypted signature, crack if you can