{"id":933,"date":"2005-10-17T12:00:00","date_gmt":"2005-10-17T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=933"},"modified":"2005-10-17T12:00:00","modified_gmt":"2005-10-17T12:00:00","slug":"ansi-join-syntax","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/10\/17\/ansi-join-syntax\/","title":{"rendered":"ANSI Join Syntax"},"content":{"rendered":"

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

To give the briefest of introductions, from Oracle 9i, instead of using this SQL<\/p>\n

SELECT d.dname, e.empnoFROM dept d, emp eWHERE d.deptno = e.deptno;<\/pre>\n
You can use this<\/p>\n
SELECT d.dname, e.empnoFROM dept d JOIN emp e USING (deptno);<\/pre>\n
Or this<\/p>\n
SELECT d.dname, e.empnoFROM dept d JOIN emp eON (d.deptno = e.deptno);<\/pre>\n
And instead of this pre-9i outer join<\/p>\n
SELECT d.dname, e.empnoFROM dept d, emp eWHERE d.deptno = e.deptno (+);<\/pre>\n
You can use this<\/p>\n
SELECT d.dname, e.empnoFROM dept d LEFT JOIN emp eUSING (deptno);<\/pre>\n
Or this<\/p>\n
SELECT d.dname, e.empnoFROM dept d LEFT JOIN emp eON (d.deptno = e.deptno);<\/pre>\n
(For more info, I googled ‘Oracle ANSI join syntax’ and near the top of the results was this article<\/a> by Sanjay Mishra.)<\/p>\n
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<\/a> was ANSI join syntax. When I first saw this I thought :-<\/p>\n
‘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!’<\/p>\n
Of course Oracle included the new syntax in the 9i OCP exam that covers SQL and PL\/SQL, because it\ufffds clearly such a critical part of being a 9i DBA, right? (No further comment.)<\/p>\n
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<\/p>\n