SQL question - GoFuckYourself.com

alex79 · 03-22-2006, 03:03 AM

What is the difference between

Code:

Select * from tablea a join tableb b on a.id=b.id
        where id=100

and

Code:

Select * from tablea a join tableb b on a.id=b.id and a.id=100

Is the second sintax faster? in first case the script will realise the join after all rows from tablea and after the join is complete will filter after the id=100 and is second case the join will be realised only after the row where id=100? (if yes then second case i think is much faster)

any opinions?

woj · 03-22-2006, 04:38 AM

Your best bet is to just make up some tables, and test it real quick...

pstation · 03-22-2006, 04:52 AM

They're essentially the same, if you're using MySQL you can prepend the query with "EXPLAIN" to view more information about the query.

03-22-2006, 03:03 AM	#1
alex79 Confirmed User Join Date: Jun 2002 Location: france Posts: 996	SQL question What is the difference between Code: Select * from tablea a join tableb b on a.id=b.id where id=100 and Code: Select * from tablea a join tableb b on a.id=b.id and a.id=100 Is the second sintax faster? in first case the script will realise the join after all rows from tablea and after the join is complete will filter after the id=100 and is second case the join will be realised only after the row where id=100? (if yes then second case i think is much faster) any opinions? __________________

03-22-2006, 04:38 AM	#2
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	Your best bet is to just make up some tables, and test it real quick... __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager

03-22-2006, 04:52 AM	#3
pstation Confirmed User Join Date: Jul 2003 Location: chicago Posts: 1,135	They're essentially the same, if you're using MySQL you can prepend the query with "EXPLAIN" to view more information about the query.