SQL Query Joins

Question

SELECT * FROM a JOIN b ON table_a.id = b.id WHERE table_a.status ='success'

SELECT * FROM table_a, b WHERE table_a.id = b.id AND table_a.status ='success'

Why is the second format used and not the first one?? Is it due to performance reasons?

If yes then how or why. Please explain in detail.

Gowtham K · Answer

Hi Kritika Please close the question by accepting it

Kritika Singh · Answer

ohhhk....thnks fr the help

Francis · Answer

If the second one is costlier then shouldn't we use the first one?? We can go with first one. Also you should note the point in the Stackoverflow discussion: If a WHERE clause is added to a cross join it behaves as an inner join as the WHERE imposes a limiting factor. So in your case both queries are behaves same. Hope this helps!

Kritika Singh · Answer

If the second one is costlier then shouldn't we use the first one?? In the link it is stated that the second one is ANSI standard. Does it make any difference??

Francis · Answer

Why is the second format used and not the first one?? Is it due to performance reasons? The first query uses inner join and the second query creates cross join. The cross join is costlier than inner join. Here is the good discussion on this : http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join Hope this helps!