Hi,
I'm creating a query that will display information for a record which is derived from 8 tables. The developer who originally wrote the query used a combination of 'where this equals this' AND 'this equals this' to create the joins.
I have since changed the query to use INNER JOINS. I wondered if my approach was better than utilising a combination of WHERE operators.
On a measure of good practice, is a combination of INNER JOINS a good option or should I be employing a different technique.
From performance point of view, there will not be any difference... atleast not on prominent RDBMS like Sql-server / Oracle... These Database Engine, are capable of identifying both the patterns means the same and use the same execution plan for both...
In my humble opinion both are equally good practice, but you should maintain consistency and proper alignment... Somewhere I heard that where clause are generally used by oracle developers, and inner join by Sql-Server... Not very much sure about that... By now most of the coders are capable of understanding both types of queries...
Both will behave the same (as The King said). Personally I prefer the INNER/OUTER/LEFT JOIN syntax because its more intuitive/explicit. When you get into LEFT JOINs with join conditions in the WHERE clause then you have to get into using plus signs and then you have to remember which side to put them on. Urgghh.
It also seems (again as The King said) to be true that Oracle devs favour putting the join conditions into the WHERE clause.
-Jamiet
I beleive that there is no performance difference between both :
ANSI Style
SELECT * FROM Table1 a JOIN TABLE2 b on a.id = b.id
Old Style
SELECT * FROM Table1 a , TABLE2 b
WHERE a.id = b.id
The ANSI style is newer and more readable and pretty, i do prefer the old style especially when it comes to join more than 4/5 Tables... maybe because im an Oracle Dev as it was said before o_O