ANSI vs. Oracle Proprietary SQL

On Tuesdays Cary Millsap runs a meeting for Method-R customers to chat about Oracle stuff. Often he has something prepared, but this Tuesday he didn’t. Doug Gault decided to share an image that finally helped him get his head around the ANSI SQL syntax. Doug has been around the Oracle world for a long time but he’s always been able to work with Oracle proprietary SQL so he never really learned the ANSI SQL syntax. Recently he got assigned to a project where ANSI SQL is mandated so he had to get everything straight in his head. He shared an image that he had created from some training and we all took a look at it. Me, being me, I immediately jumped in with what I thought would be improvements to the image. I was challenged to come up with a better image, and so, I created the below.

My hope is that this will help some folks move away from the horrible (in my opinion) Oracle propriety SQL syntax to the totally awesome ANSI SQL syntax. I think the Oracle syntax is horrible because where clauses in queries end up doing two things; joining table AND filtering rows. With the ANSI syntax, join clauses join tables and where clauses only filter rows.

A note on the above: I used the preferred USING syntax to join tables for the ANSI queries:

join using (deptno)

instead of the ON syntax to join tables

join on e.deptno = d.deptno

I believe this is easier to read and understand and, in general, less code is better code, and this is smaller. If you use the USING syntax just note that you no longer associate the column with one table or another in the other clauses (like SELECT or WHERE) but instead leave it unconstrained. For example:

select deptno, e.ename, d.dname
  from emp e
  join dept d using (deptno)
 where deptno > 20;

If you were to qualify the DEPTNO column in either the select clause or the where clause (d.deptno for example) you’d get an ORA-25154: column part of USING clause cannot have qualifier message.

Let me know if this is helpful for you!