Oracle and ANSI SQL Joins

Oracle Community

Oracle and ANSI SQL Joins

Standards are a beautiful thing. Imagine the confusion if everyone with whom you work spoke a different language. It would simply be too difficult to accomplish anything. The workplace would come to a standstill. An agreed-upon standard for communication and interaction is essential for the smooth flow of information and processes. The greatest technical victories of the last three decades are all due to simple standards (the PC, C, HTTP and HTML, TCP, XML and LDAP).

SQL is one such simple standard. Over time Oracle innovations to database access bubble their way up to the ANSI standard for SQL. Unfortunately, the ANSI approach frequently diverges from Oracle's, and new SQL syntax must be learned. One used to be able to survive as an Oracle professional without learning the ANSI standard. Those days are over.

Students are being trained more and more in ANSI standard SQL these days, making relics out of those of us who stick to Oracle's dialect. Second, ANSI SQL joins tend to be more straightforward than Oracle joins. Once the initial learning curve is crested, ANSI SQL joins are clearer and less error-prone, less likely to result in Cartesian products or other incorrect relationships. Finally, one of the first advantages touted for ANSI SQL joins is the ability to easily perform full outer joins; instead of using the clunky UNION approach in the Oracle dialect.

In short,  we recommend using ANSI JOINs for several reasons (adapted from a live-audience Q&A with Bert Scalzo following the webcast “Pinpoint and Optimize Inefficient SQL with Toad™ for Oracle®.

  1. Oracle recommends/advises it.
  2. It's better to use ANSI standards than Oracle’s proprietary alternatives.
  3. Some problems cannot be answered/coded unless you use an ANSI JOIN.
  4. Some things in ANSI JOIN are far superior to the non-ANSI alternative. FULL OUTTER JOIN, for example, requires two queries and UNION.
  5. It's the current vs. 1990s way of doing things.
  6. Once you get used to ANSI JOIN, the code is far easier to read, especially for long or complex queries.

So in the spirit of cooperation, teamwork and dedication to the progress that simple standards foster, we recommend that you begin learning and incorporating the ANSI SQL standard today. Here are a few side-by-side examples of Oracle SQL and ANSI SQL joins (old style on the left, ANSI style on the right):

If you actually had the need to write a Cartesian product, you would use the CROSS JOIN.

SELECT ename,
       job,
       dname
  FROM emp,
       dept;
SELECT ename,
       job,
       dname
  FROM emp
       CROSS JOIN dept;

If the join key between the joined tables is consistently named, you can let the NATURAL JOIN or JOIN...USING handle the join for you.

SELECT e.ename,
       e.job,
       d.dname
  FROM emp  e,
       dept d
 WHERE e.deptno = d.deptno;
SELECT e.ename,
       e.job,
       d.dname
  FROM emp e
       NATURAL JOIN dept d;

Or

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

What if you need to join more than one table? No problem, keep adding ANSI join clauses under the FROM. ANSI SQL is particular about join order, but Oracle will nudge you in the right direction.

SELECT e.ename,
       d.dname,
       b.JOB,
       b. sal,
       b.comm
  FROM emp   e,
       dept  d,
       bonus b
 WHERE d.deptno = e.deptno
   AND e.ename = b.ename;
SELECT e.ename,
       d.dname,
       b.JOB,
       b. sal,
       b.comm
  FROM emp e
  JOIN dept d ON (d.deptno = e.deptno)
  JOIN bonus b ON (e.ename = b.ename);

To perform a simple outer join in Oracle, you use the "(+)" operator on the side of the join where you want non-matching rows to return NULLs. In ANSI, you use either LEFT OUTER or RIGHT OUTER to indicate on which side of the join you wish the non-matching NULL values to appear.

SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e,
       dept  d
 WHERE e.deptno = d.deptno(+);
SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e
  LEFT OUTER JOIN dept d USING (deptno);
SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e,
       dept  d
 WHERE e.deptno(+) = d.deptno;
SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e
 RIGHT OUTER JOIN dept d USING (deptno);
SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e,
       dept  d
 WHERE e.deptno = d.deptno(+)
UNION
SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e,
       dept  d
 WHERE e.deptno(+) = d.deptno;
SELECT e.ename,
       d.dname,
       e.job
  FROM emp   e
  FULL OUTER JOIN dept d USING (deptno);
14437 1 /
Follow / 31 Mar 2016 at 9:20pm

"ANSI Join" is a misnomer.  "WHERE d.deptno = e.deptno AND e.ename = b.ename" is ANSI SQL.  It is ANSI-89 compliant.  You are really comparing and contrasting one ANSI standard against another.