From the book, Mastering Oracle SQL and SQL *Plus, Chapter 8, Apress, January 2005.

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5

8.3 Alternative ANSI/ISO Standard Join Syntax

The join examples shown in the previous section use the Cartesian product operator (the comma in the FROM clause) as a starting point, and then filter the rows using an appropriate WHERE clause. There’s absolutely nothing wrong with that approach, and the syntax is fully compliant with the ANSI/ISO SQL standard, but the ANSI/ISO SQL standard also supports alternative syntax to specify joins. This alternative join syntax is covered in this section.

First, let’s look again at the join statement in Listing 8-7. You could argue that the WHERE clause of that query contains two different condition types: line 5 contains the join condition to make sure you combine the right rows, and line 6 is a “real” (nonjoin) condition to filter the employees based on their birth dates.

Listing 8-8 shows an equivalent query, producing the same results, using a different join syntax. Note the keywords JOIN and ON. Also note also that this join syntax doesn’t use any commas in the FROM clause.

SQL> select e.ename as employee
  2  ,      m.ename as manager
  3  from   employees m
  4         JOIN
  5         employees e
  6         ON e.mgr = m.empno
  7  where  e.bdate > date '1965-01-01'
  8  order  by employee;

EMPLOYEE MANAGER
-------- --------
ADAMS    SCOTT
CLARK    KING
JONES    BLAKE
JONES    KING
SMITH    FORD
TURNER   BLAKE

6 rows selected.

SQL>

Listing 8-8: JOIN . . . ON example.

The syntax of Listing 8-8 is more elegant than the syntax in Listing 8-7, because the join is fully specified in the FROM clause and the WHERE clause contains only the nonjoin condition.

Natural Joins

You can also use the NATURAL JOIN operator in the FROM clause. Listing 8-9 shows an example that joins the EMPLOYEES table with the HISTORY table.

Question: Before reading on, how is it possible that Listing 8-9 produces 15 rows in the result, instead of 14?

SQL> select ename, beginyear, msal, deptno
  2  from   employees
  3         natural join
  4         history;

ENAME    BEGINYEAR     MSAL   DEPTNO
-------- --------- -------- --------
SMITH         2000      800       20
ALLEN         1999     1600       30
WARD          1992     1250       30
WARD          2000     1250       30
JONES         1999     2975       20
MARTIN        1999     1250       30
BLAKE         1989     2850       30
CLARK         1988     2450       10
SCOTT         2000     3000       20
KING          2000     5000       10
TURNER        2000     1500       30
ADAMS         2000     1100       20
JONES         2000      800       30
FORD          2000     3000       20
MILLER        2000     1300       10

15 rows selected.

SQL>

Listing 8-9: Natural Join example.

Explanation: To understand what’s happening in Listing 8-9, you must know how the NATURAL JOIN operator is defined in the SQL language. Listing 8-9 illustrates the behavior of the NATURAL JOIN operator:

1. The NATURAL JOIN operator determines which columns the two tables (EMPLOYEES and HISTORY) have in common. In this case, these are the three columns EMPNO, MSAL, and DEPTNO.
2. It joins the two tables (using an equijoin) over all columns they have in common.
3. It suppresses the duplicate columns resulting from the join operation in the previous step. This is why you don’t get an error message about MSAL and DEPTNO in the SELECT clause being ambiguously defined.
4. Finally, the NATURAL JOIN operator evaluates the remaining query clauses. In Listing 8-9, the only remaining clause is the SELECT clause. The final result shows the desired four columns.

Apparently, every employee occurs only once in the result, except WARD. This means that this employee has been employed by the same department (30) for the same salary (1250) during two distinct periods of his career. This is a pure coincidence. If the query had returned 14 rows instead of 15, we would probably not have been triggered to investigate the query for correctness. Remember that some wrong queries may give “correct” results by accident.

This example shows that you should be very careful when using the NATURAL JOIN operator. Probably the biggest danger is that a natural join may “suddenly” start producing strange and undesirable results if you add new columns to your tables, or you rename existing columns, thus accidentally creating matching column names.

Caution: Natural joins are safe only if you practice a very strict column-naming standard in your database designs.

Equijoins on Columns with the Same Name

SQL offers an alternative way to specify equijoins, allowing you to explicitly specify the columns you want to participate in the equijoin operation. As you saw in Listing 8-8, you can use the ON clause followed by fully specified join predicates. You can also use the USING clause, specifying column names instead of full predicates. See Listing 8-10 for an example.

SQL> select e.ename, e.bdate
  2  ,      h.deptno, h.msal
  3  from   employees e
  4         join
  5         history h
  6         using (empno)
  7  where  e.job = 'ADMIN';

ENAME    BDATE         DEPTNO     MSAL
-------- ----------- -------- --------
JONES    03-DEC-1969       30      800
MILLER   23-JAN-1962       10     1275
MILLER   23-JAN-1962       10     1280
MILLER   23-JAN-1962       10     1290
MILLER   23-JAN-1962       10     1300

SQL>

Listing 8-10: JOIN ... USING example.

Note that you need tuple variables again, because you join over only the EMPNO column; the columns h.DEPTNO and e.DEPTNO are now different.

Figure 8-3 shows the syntax diagram of the ANSI/ISO join syntax, including the NATURAL JOIN operator, the ON clause, and the USING clause.

Figure 8-3: ANSI/ISO join syntax diagram.

Note that you can also use a CROSS JOIN syntax. The result is identical to the effect of the comma operator in the FROM clause: the Cartesian product.

The examples in the remainder of this book will show a mixture of “old-fashioned” joins (as introduced in Section 8.2) and the alternative ANSI/ISO SQL join syntax explained in this section.

8.4 Outer Joins

Earlier in the chapter, in Listing 8-4, we executed a regular join (an equijoin) similar to the one shown in Listing 8-11.

SQL> select d.deptno, d.location
  2  ,      e.ename, e.init
  3  from   employees e, departments d
  4  where  e.deptno = d.deptno
  5  order  by d.deptno, e.ename;

  DEPTNO LOCATION ENAME    INIT
-------- -------- -------- -----
      10 NEW YORK CLARK    AB
      10 NEW YORK KING     CC
      10 NEW YORK MILLER   TJA
      20 DALLAS   ADAMS    AA
      20 DALLAS   FORD     MG
      20 DALLAS   JONES    JM
      20 DALLAS   SCOTT    SCJ
      20 DALLAS   SMITH    N
      30 CHICAGO  ALLEN    JAM
      30 CHICAGO  BLAKE    R
      30 CHICAGO  JONES    R
      30 CHICAGO  MARTIN   P
      30 CHICAGO  TURNER   JJ
      30 CHICAGO  WARD     TF

14 rows selected.

SQL>

Listing 8-11: Regular Join.

The result in Listing 8-11 shows no rows for department 40, for an obvious reason: that department does exist in the DEPARTMENTS table, but it has no corresponding employees. In other words, if tuple variable d refers to department 40, there is not a single row e in the EMPLOYEES table to make the WHERE clause evaluate to TRUE.

If you want the fact that department 40 exists to be reflected in your join results, you can make that happen with an outer join. For outer joins in Oracle, you can choose between two syntax options:

• The “old” outer join syntax, supported by Oracle since many releases, and implemented many years before the ANSI/ISO standard defined a more elegant outer join syntax
• The ANSI/ISO standard outer join syntax

We will discuss an example of both outer join syntax variants, based on the regular join in Listing 8-11.

Old Oracle-Specific Outer Join Syntax

First, change the fourth line of the command in Listing 8-11 and add a plus sign between parentheses, as shown in Listing 8-12.

SQL> select d.deptno, d.location
  2  ,      e.ename, e.init
  3  from   employees e, departments d
  4  where  e.deptno(+) = d.deptno
  5  order  by d.deptno, e.ename;

  DEPTNO LOCATION ENAME    INIT
-------- -------- -------- -----
      10 NEW YORK CLARK    AB
      10 NEW YORK KING     CC
      10 NEW YORK MILLER   TJA
      20 DALLAS   ADAMS    AA
      20 DALLAS   FORD     MG
      20 DALLAS   JONES    JM
      20 DALLAS   SCOTT    SCJ
      20 DALLAS   SMITH    N
      30 CHICAGO  ALLEN    JAM
      30 CHICAGO  BLAKE    R
      30 CHICAGO  JONES    R
      30 CHICAGO  MARTIN   P
      30 CHICAGO  TURNER   JJ
      30 CHICAGO  WARD     TF
      40 BOSTON

15 rows selected.

SQL>

Listing 8-12: The (+) Outer Join syntax.

As you can see, department 40 now also appears in the result. The effect of the addition (+) in the WHERE clause has combined department 40 with two null values for the employee data. The main disadvantage of this outer join syntax is that you must make sure to add the (+) operator in the right places in your SQL command. Failing to do so normally results in disabling the outer join effect. Another disadvantage of this outer join syntax is its lack of readability.

New Outer Join Syntax

The new ANSI/ISO outer join syntax is much more elegant and readable. Listing 8-13 shows the version to get the same results as in Listing 8-12.

SQL> select deptno, d.location
  2  ,      e.ename, e.init
  3  from   employees e
  4         right outer join
  5         departments d
  6         using (deptno)
  7  order  by deptno, e.ename;

  DEPTNO LOCATION ENAME    INIT
-------- -------- -------- -----
      10 NEW YORK CLARK    AB
      10 NEW YORK KING     CC
      10 NEW YORK MILLER   TJA

      20 DALLAS   ADAMS    AA
      20 DALLAS   FORD     MG
      20 DALLAS   JONES    JM
      20 DALLAS   SCOTT    SCJ
      20 DALLAS   SMITH    N
      30 CHICAGO  ALLEN    JAM
      30 CHICAGO  BLAKE    R
      30 CHICAGO  JONES    R
      30 CHICAGO  MARTIN   P
      30 CHICAGO  TURNER   JJ
      30 CHICAGO  WARD     TF
      40 BOSTON

15 rows selected.

SQL>

Listing 8-13: ANSI/ISO Outer Join example.

In Listing 8-13 we used a RIGHT OUTER JOIN, because we suspect the presence of rows at the right-hand side (the DEPARTMENTS table) without corresponding rows at the left-hand side (the EMPLOYEES table). If you switched the two table names in the FROM clause, you would need the LEFT OUTER JOIN operator. Oracle also supports the FULL OUTER JOIN syntax, where both tables participating in the join operation handle rows without corresponding rows on the other side in a special way. Figure 8-4 shows all three outer join syntax possibilities.

Figure 8-4: ANSI/ISO outer join syntax diagram.

The outer join operator is especially useful if you want to aggregate (summarize) data; for example, when you want to produce a course overview showing the number of attendees for each scheduled course. In such an overview, you obviously also want to see all scheduled courses for which no registrations are entered yet, so you might consider canceling or postponing those courses. This type of query (with aggregation) is the topic of Section 8.5.

Outer Joins and Performance

Although outer joins obviously imply some additional processing for the DBMS, there is no reason to avoid outer joins for performance reasons. The Oracle optimizer knows how to handle outer joins efficiently. Moreover, given a certain data model, you sometimes need outer joins. Don’t try to invent your own workarounds in such cases, and don’t believe unfounded statements like “outer joins are bad.”

In Section 8.9, we will revisit outer joins to discuss partitioned outer joins.