ANSI Join Syntax

A couple of days ago one of my colleagues raised the question - 'ANSI join syntax, does anyone actually use that?' I have pretty strong views on this and so a brief dynamic conversation followed (and that isn't a euphemism for 'stand-up row', I should add!) So, what about ANSI join syntax?

To give the briefest of introductions, from Oracle 9i, instead of using this SQL

SELECT d.dname, e.empno
FROM dept d, emp e
WHERE d.deptno = e.deptno;

You can use this

SELECT d.dname, e.empno
FROM dept d JOIN emp e 
USING (deptno);

Or this

SELECT d.dname, e.empno
FROM dept d JOIN emp e
ON (d.deptno = e.deptno);

And instead of this pre-9i outer join

SELECT d.dname, e.empno
FROM dept d, emp e
WHERE d.deptno = e.deptno (+);

You can use this

SELECT d.dname, e.empno
FROM dept d LEFT JOIN emp e
USING (deptno);

Or this

SELECT d.dname, e.empno
FROM dept d LEFT JOIN emp e
ON (d.deptno = e.deptno);

(For more info, I googled 'Oracle ANSI join syntax' and near the top of the results was this article by Sanjay Mishra.)

When Oracle 9i was released I was still teaching for Learning Tree and whenever Oracle released a new major release of the rdbms, new versions of the core courses were developed. One of the key additions to the introduction course was ANSI join syntax. When I first saw this I thought :-

'Brilliant! Another thing to learn and what does it give us anyway? Database-independent applications are one of the more dangerous myths still floating around the IT industry. It also looks terribly like that long-winded ugly SQL that MS Access throws at Oracle databases I've supported. Yuck!'

Of course Oracle included the new syntax in the 9i OCP exam that covers SQL and PL/SQL, because it�s clearly such a critical part of being a 9i DBA, right? (No further comment.)

Then again, I'm not at all averse to learning new things and as my job was to help people learn Oracle, I had to review it properly. As I spent more time playing around with it and then teaching it, it occurred to me that I actually quite liked this alternative! The main strengths of ANSI join syntax are

• A Full Outer Join is straightforward and easy to understand when looking at the code

• The join conditions are specified where they make most sense - in the FROM clause, with the tables that you are joining.

• Although still possible, it is more unlikely that you will miss one or two join conditions in a long multi-table query. When we were discussing it, my colleague's response to this was along the lines of, 'well you wouldn't, would you?' I've had to debug enough long SQL statements with missing join conditions to prove this wrong. The problem is that there is absolutely no proper placement for join conditions in Oracle's original syntax. They can appear anywhere in the where clause, in any order, all mixed in with business logic predicates. Of course, good SQL developers impose some ordering themselves, to make their queries more readable and maintainable, but there's nothing to force them to do this. The punch-line is that it's not the good SQL developers who make the most mistakes

And the Cons?

• Depending on the number of tables being joined, the join conditions and the particular variation of the syntax that you use, the ANSI approach can be verbose and lead to more typing. (This is probably the one that put me off most at first.)

• It's only available from 9i so if you are working with earlier versions (which many of us are, regardless of what may or may not be supported) then you would need to chop-and-change between methods. (This is the probably the biggest down-side for me now)

• I, my colleagues and most other Oracle developers and DBAs are far more familiar and comfortable with the traditional Oracle approach. (I'm sure that this is the number one reason why most people I know still use the Oracle syntax!)

When you look at it, the Pros seem to be more tangible than the Cons. I struggle to defend the traditional Oracle join syntax versus the ANSI syntax. As you can imagine, I've moved over to ANSI syntax whenever I'm using a version > 9i.

Of course, I haven't

Any other opinions on this?