Team LiB   Previous Section   Next Section

3.6 ANSI-Standard Join Syntax in Oracle9i

Oracle9i introduced new join syntax that is compliant to the ANSI SQL standard defined for SQL/92. Prior to Oracle9i, Oracle supported the join syntax defined in the SQL/86 standard. In addition, Oracle supported outer joins through the proprietary outer join operator (+), discussed earlier in this chapter. The old join syntax and the proprietary outer join operator are still supported in Oracle9i. However, the ANSI standard join syntax introduces several new keywords and new ways to specify joins and join conditions.

3.6.1 New Join Syntax

With the traditional join syntax, you specify multiple tables in the FROM clause separated by commas, as in the following example: 

SELECT L.LOCATION_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D, LOCATION L
WHERE D.LOCATION_ID = L.LOCATION_ID;

With the new syntax in Oracle9i, you specify the join type with the JOIN keyword in the FROM clause. For example, to perform an inner join between tables DEPARTMENT and LOCATION, you specify: 

FROM DEPARTMENT D INNER JOIN LOCATION L

In the traditional join syntax, the join condition is specified in the WHERE clause. With the new syntax in Oracle9i, the purpose of the WHERE clause is for filtering only. The join condition is separated from the WHERE clause and put in a new ON clause, which appears as part of the FROM clause. The join condition of the previous example will be specified using the new syntax as: 

ON D.LOCATION_ID = L.LOCATION_ID;

The complete join, using the new syntax, will be:

SELECT L.LOCATION_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D INNER JOIN LOCATION L
ON D.LOCATION_ID = L.LOCATION_ID;

Specifying the join condition is further simplified if:

  • You use equi-joins, and

  • The column names are identical in both the tables.

If these two conditions are satisfied, you can apply the new USING clause to specify the join condition. In the previous example, we used an equi-join. Also, the column involved in the join condition (LOCATION_ID) is named identically in both the tables. Therefore, this join condition can also be written as: 

FROM DEPARTMENT D INNER JOIN LOCATION L
USING (LOCATION_ID);

The USING clause affects the semantics of the SELECT clause as well. The USING clause tells Oracle that the tables in the join have identical names for the column in the USING clause. Now, Oracle merges those two columns and recognizes only one such column. If you have included the join column in the SELECT list, Oracle doesn't allow you to qualify the column with a table name (or table alias). Our SELECT clause, then, needs to appear as follows: 

SELECT LOCATION_ID, D.NAME, L.REGIONAL_GROUP

The complete syntax with the USING clause will be:

SELECT LOCATION_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D INNER JOIN LOCATION L
USING (LOCATION_ID);

If you attempt to qualify the join column name in the SELECT list using either an alias or a table name, you will get an error: 

SELECT L.LOCATION_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D INNER JOIN LOCATION L
USING (LOCATION_ID);
SELECT L.LOCATION_ID, D.NAME, L.REGIONAL_GROUP
       *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

The behavior of USING contrasts with the traditional join syntax, in which you must qualify the identical column names with the table name or table alias.

If a join condition consists of multiple columns, you need to specify all the column conditions in the ON clause separated by AND. For example, if tables A and B are joined based on columns c1 and c2, the join condition would be: 

SELECT ...
FROM A INNER JOIN B 
ON A.c1 = B.c1 AND A.c2 = B.c2

If the column names are identical in the two tables, you can use the USING clause and specify all the columns in one USING clause, separated by commas. The previous join condition can be rewritten as: 

SELECT ...
FROM A INNER JOIN B 
USING (c1, c2)

Cross Joins

An advantage of the new join syntax is that you can't accidentally generate a Cartesian product by omitting join conditions. But what if you really do want a Cartesian product? Are you forced to fall back on the old join syntax? That's certainly an option, but a better approach is to explicitly specify a cross join. The term cross join is simply an alternative reference to Cartesian product.

In Oracle9i, you can explicitly request a cross join by using the CROSS JOIN keywords: 

SELECT *
FROM A CROSS JOIN B;

The advantage of this new syntax is that it makes your request for a cross join (or Cartesian product) explicit. Cartesian products are usually mistakes, and future maintenance programmers may be tempted to correct such "mistakes." The explicit CROSS JOIN syntax indicates to future maintenance programmers that a Cartesian product is not an oversight.

The new join syntax doesn't allow you to accidentally forget the join condition while performing a join, and thereby helps prevent you from accidentally generating a Cartesian product. When you specify any of the new join keywords in the FROM clause, you tell Oracle that you are going to perform a JOIN, and Oracle insists that you specify the join condition in an ON or USING clause.

3.6.2 ANSI Outer Join Syntax

We discussed Oracle's traditional outer join syntax earlier in this chapter. The ANSI outer join syntax doesn't use the outer join operator (+) in the join condition; rather, it specifies the join type in the FROM clause. The syntax of ANSI outer join is: 

FROM table1 { LEFT | RIGHT | FULL } [OUTER] JOIN table2

The syntax elements are:

table1, table2

Specifies the tables on which you are performing the outer join.

LEFT

Specifies that the results be generated using all rows from table1. For those rows in table1 that don't have corresponding rows in table2, NULLs are returned in the result set for the table2 columns. This is the equivalent of specifying (+) on the table2 side of the join condition in the traditional syntax.

RIGHT

Specifies that the results be generated using all rows from table2. For those rows in table2 that don't have corresponding rows in table1, NULLs are returned in the result set for the table1 columns. This is the equivalent of specifying (+) on the table1 side of the join condition in the traditional syntax.

FULL

Specifies that the results be generated using all rows from table1 and table2. For those rows in table1 that don't have corresponding rows in table2, NULLs are returned in the result set for the table2 columns. Additionally, for those rows in table2 that don't have corresponding rows in table1, NULLs are returned in the result set for the table1 columns. There is no equivalent in the traditional syntax for a FULL OUTER JOIN.

OUTER

Specifies that you are performing an OUTER join. This keyword is optional. If you use LEFT, RIGHT, or FULL, Oracle automatically assumes an outer join. The OUTER is for completeness sake, and complements the INNER keyword.

To perform a LEFT OUTER JOIN between the DEPARTMENT and LOCATION tables, you can use: 

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D LEFT OUTER JOIN LOCATION L
ON D.LOCATION_ID = L.LOCATION_ID;

      DEPT_ID NAME           REGIONAL_GROUP
------------- -------------- --------------------
           10 ACCOUNTING     NEW YORK
           20 RESEARCH       DALLAS
           30 SALES          CHICAGO
           40 OPERATIONS     BOSTON
           12 RESEARCH       NEW YORK
           13 SALES          NEW YORK
           14 OPERATIONS     NEW YORK
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
           34 OPERATIONS     CHICAGO
           43 SALES          BOSTON
           50 MARKETING
           60 CONSULTING

13 rows selected.

This query lists all the rows from the DEPARTMENT table and the corresponding locations from the LOCATION table. For the rows from DEPARTMENT with no corresponding rows in LOCATION, NULLs are returned in the L.REGIONAL_GROUP column in the result set. It is equivalent to the following traditional outer join query: 

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D, LOCATION L
WHERE D.LOCATION_ID = L.LOCATION_ID (+);

To perform a RIGHT OUTER JOIN between the DEPARTMENT and LOCATION tables, you can use: 

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D RIGHT OUTER JOIN LOCATION L
ON D.LOCATION_ID = L.LOCATION_ID;

      DEPT_ID NAME           REGIONAL_GROUP
------------- -------------- --------------------
           10 ACCOUNTING     NEW YORK
           12 RESEARCH       NEW YORK
           14 OPERATIONS     NEW YORK
           13 SALES          NEW YORK
           30 SALES          CHICAGO
           34 OPERATIONS     CHICAGO
           20 RESEARCH       DALLAS
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
                             SAN FRANCISCO
           40 OPERATIONS     BOSTON
           43 SALES          BOSTON

12 rows selected.

This query lists all the rows from the LOCATION table, and their corresponding departments from the DEPARTMENT table. For the rows from LOCATION that don't have corresponding rows in DEPARTMENT, NULLs are returned in D.DEPT_ID and D.NAME columns in the result set. This query is equivalent to the following traditional outer join query: 

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D, LOCATION L
WHERE D.LOCATION_ID (+) = L.LOCATION_ID;

If you want to include the departments without a location, as well as the locations without a department, you need to do a full outer join: 

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D FULL OUTER JOIN LOCATION L
ON D.LOCATION_ID = L.LOCATION_ID;

      DEPT_ID NAME           REGIONAL_GROUP
------------- -------------- --------------------
           10 ACCOUNTING     NEW YORK
           12 RESEARCH       NEW YORK
           13 SALES          NEW YORK
           14 OPERATIONS     NEW YORK
           20 RESEARCH       DALLAS
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
           30 SALES          CHICAGO
           34 OPERATIONS     CHICAGO
           40 OPERATIONS     BOSTON
           43 SALES          BOSTON
           50 MARKETING
           60 CONSULTING
                             SAN FRANCISCO
14 rows selected.

We have seen earlier in this chapter that you can't perform a full outer join using the (+) operator on both sides in the join condition. In Section 3.2.2, we showed how you can circumvent this restriction by using a UNION query. With the new syntax in Oracle9i, you no longer need to perform a UNION query to do a full outer join. The new syntax is not only ANSI-compliant, it is elegant and efficient as well.

3.6.3 Advantages of the New Join Syntax

The new join syntax represents a bit of an adjustment to developers who are used to using Oracle's traditional join syntax, including the outer join operator (+). However, there are several advantages of using the new syntax:

  • The new join syntax follows the ANSI standard and therefore makes your code more portable.

  • The new ON and USING clauses help in separating the join conditions from other filter conditions in the WHERE clause. This enhances development productivity and maintainability of your code.

  • The new syntax makes it possible to perform a full outer join without having to perform a UNION of two SELECT queries.

We recommend that while working with Oracle9i, you use the new join syntax instead of the traditional join syntax.

    Team LiB   Previous Section   Next Section