Oracle Join Syntax for ANSI people
18/07/2013 6 Comments
I was talking with a non-Oracle DBA the other day about Oracle SQL syntax for joins, as I had written some SQL which he didn’t understand, using the old form of Oracle notation to signify outer joins – the (+) operator. It got me thinking; I have been using Oracle for a very long time, before ANSI Join syntax was allowed. I therefore tend to use the Oracle-specific format for joins as my brain it wired to simply understand the notation. However, I understand the standard ANSI syntax as I do use and administer other RDBMS systems (maily SQL Server, but I am also familiar with the *= syntax used by Sybase.)
I was wondering, which syntax do you use? If you use the ANSI standard joins with Oracle, have you come across any drawbacks or problems; I seem to recall some anecdotes but can’t put my finger on any?
Examples of the Syntax:
--Inner Join ANSI
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def join dba_users usr on (def.username = usr.username)
order by 2
--Inter Join Oracle
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def, dba_users usr
where def.username = usr.username
order by 2
--Right outer join ANSI
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def right outer join dba_users usr on (def.username = usr.username)
order by 2
-- Right outer join oracle
-- put the (+) where you want to say "match regardless" [on the "wrong" side]
select def.username,usr.username,usr.account_status
from dba_users_with_defpwd def, dba_users usr
where def.username(+) = usr.username
order by 2
Neil Chandler's DB Blog 
I use the ANSI joins exclusively now. For Outer joins, I only use LEFT OUTER JOIN, never RIGHT.
I believe there were a few bugs in 10g. I think I hit one in 10gR1, but not since.
I prefer the syntax as it ensures I’ve thought about how the tables are linked (making sure they are ALL linked) and what I expect the driving table to be.
As a caveat, it doesn ‘t do automatic ‘sanity’ checks. It is syntactically valid to have
select …
from table_a a
join table_b b on a.id = b.id
join table_c c on a.val = b.val
(IE, the layout suggest C is linked in when it isn’t).
LikeLike
I wasn’t aware that not referencing C in the predicate was valid. Definite curve-ball when doing copy/paste of code.
Neil
LikeLike
There are subtle differences between ANSI Outer Joins and the Oracle Native syntax. The Oracle Optimizer does not directly support the ANSI Join syntax, and instead it rewrites your SQL replacing each ANSI Join by an Oracle native join. But it does this in a way that means that you can end up with a much more complex query involving nested views and outer joins – more complex than if you converted it manually. This is to do with Oracle being “safe” and wrapping some joins up inside views before joining to other data sets in the query.
I had a case of this on a large query, and the converted SQL was so complex that the Optimizer failed to find an optimal execution plan. Using a combination of the output of a 10053 trace and edited versions of the query with fewer tables in it, I could see the execution flip from a good one to a bad one when an extra table was added to the query. And this was all due to the way the Optimizer was rewriting the ANSI Outer Joins as Oracle Native Outer Joins and then which further modifications the Optimizer could make when exploring different possible execution plans.
John
LikeLike
So Oracle will rewrite the query to standard oracle syntax(!), then rewrite it to merge subqueries into joins and other options, and rewrite the whole lot into simpler terms. Add in all of the merging that Oracle will do when doing something like subquery refactoring, throw in a pinch of nesting for some analytics, and it’s no wonder the optimizer is occasionally sub-optimal.
LikeLike
I use ANSI syntax exclusively now for 2 (related) reasons.. 1) It separates out the join conditions and filter conditions (instead of throwing them all in the where clause), which to me makes the query much more readable. 2) I can’t accidentally create cartesian joins. If I write CROSS JOIN, it tells whoever is maintaining my code that yes, I did actually intend to do this.
LikeLike
You can still accidentally write a Cartesian join (see the 1st comment from Gary), but I agreed that separating your where clause from the join clauses is sensible and should make errors more obvious.
LikeLike