Search the Catalog
Oracle SQL*Plus Pocket Reference, 2nd Edition

Oracle SQL*Plus Pocket Reference, 2nd Edition

By Jonathan Gennick
October 2002
0-596-00441-9, Order Number 4419
120 pages, $12.95 US, $20.95 CA

Sample Excerpt

Table Joins (Oracle8i)

It's very common to combine data from two or more tables to return related information. Such a combination of two tables is referred to as a join.

You join two tables by listing them in the FROM clause, separated by commas. For example:

SELECT user_constraints.constraint_name, 
       user_constraints.constraint_type, 
       user_cons_columns.column_name
FROM user_constraints, user_cons_columns;

This query returns the Cartesian product--all possible combinations of all rows from both tables. Conceptually, this is where all joins start. In practice, you almost always put some conditions in the WHERE clause so that only related rows are combined. The following, more useful, query returns a list of constraint names together with the columns involved in each constraint:

SELECT user_constraints.constraint_name, 
       user_constraints.constraint_type, 
       user_cons_columns.column_name
FROM user_constraints, user_cons_columns
WHERE user_constraints.constraint_name 
      = user_cons_columns.constraint_name;

Because both tables contain columns with matching names, the column references must be qualified with the table name. You can see that this quickly gets cumbersome. The solution is to provide a shorter alias for each table and use that alias to qualify the column names. For example:

SELECT uc.constraint_name, 
       uc.constraint_type, 
       ucc.column_name
FROM user_constraints uc, 
     user_cons_columns ucc
WHERE uc.constraint_name = 
   ucc.constraint_name;

Here, the alias uc is used for the USER_CONSTRAINTS table, while UCC is used for USER_CONS_COLUMNS. The resulting query is much easier to read because you aren't overwhelmed with long table names.

Inner and outer joins

The joins that you've seen so far are inner joins. An inner join is one that returns data only when both tables have a row that matches the join conditions. For example, the following query returns only tables that have constraints defined on them:

SELECT ut.table_name, uc.constraint_name
FROM user_tables ut, user_constraints uc
WHERE ut.table_name = uc.table_name;

An outer join returns rows for one table, even when there are no matching rows in the other. You specify an outer join in Oracle by placing a plus sign (+) in parentheses following the column names from the optional table in your WHERE clause. For example:

SELECT ut.table_name, uc.constraint_name
FROM user_tables ut, user_constraints uc
WHERE ut.table_name = uc.table_name(+);

The (+) after uc.table_name makes the user_constraint table optional. The query returns all tables, and where there are no corresponding constraint records, Oracle supplies a null in the constraint name column.

Table Joins (Oracle9i)

Oracle9i introduces new table join syntax; this is the join syntax defined by the ANSI SQL/92 standard. Join conditions may now be written in the FROM clause, making it easier to follow the logic of a query. In addition, the new syntax supports full outer joins, something not possible using the old syntax. Unless you need to maintain compatibility with old releases of Oracle, I strongly recommend using the new syntax.

Sample tables

Example queries in this section on Oracle9i table joins are based on the following three tables:

SQL> SELECT *
  2  FROM course;
 
COURSE_NAME         PERIOD
--------------- ----------
Spanish I                1
Spanish 1                6
U.S. History             3
English II               4
 
SQL> SELECT *
  2  FROM enrollment;
 
COURSE_NAME         PERIOD STUDENT_NAME
--------------- ---------- ---------------
English II               4 Michael
Spanish I                1 Billy
Spanish I                6 Sky Lynn
Spanish I                1 Jeff
English II               4 Jenny
 
SQL> SELECT *
  2  FROM student;
 
STUDENT_NAME         GRADE
--------------- ----------
Michael                  6
Billy                    3
Sky Lynn                 1
Jeff                     1
Jenny                    8

Note that Sky Lynn's enrollment record is without a matching COURSE record. She is registered for Spanish I (letter "I"), while the course is Spanish 1 (digit "1"). This becomes significant when performing an outer join and when using the new USING clause.

Inner joins

Use the INNER JOIN keywords in the FROM clause to specify an inner join between the two tables. Use the ON clause to specify your join conditions. For example:

SELECT c.course_name, c.period, e.student_name
FROM course c INNER JOIN enrollment e
     ON c.course_name = e.course_name
        AND c.period = e.period;

Other clauses, such as WHERE and ORDER BY come after the FROM clause:

SELECT c.course_name, c.period, e.student_name
FROM course c INNER JOIN enrollment e
     ON c.course_name = e.course_name
        AND c.period = e.period
WHERE c.period < 9
ORDER BY c.period, c.course_name;

Join order

When joining more than two tables, use parentheses to control the join order. The following query joins COURSE and ENROLLMENT first, and then joins the table named STUDENT to the result:

SELECT c.course_name, c.period, s.student_name, s.grade
FROM (course c INNER JOIN enrollment e
        ON c.course_name=e.course_name
        AND c.period=e.period)
     INNER JOIN student s ON e.student_name=s.student_name;

If you omit parentheses, Oracle processes the joins from left to right. The example here uses parentheses to explicitly specify the default join order.

Left and right outer joins

To perform an outer join of the type traditionally supported by Oracle, use either LEFT OUTER JOIN or RIGHT OUTER JOIN. Left and right outer joins are similar; the difference lies in the ordering of the tables in the FROM clause. The following query uses the old syntax to return all rows from COURSE together with any matching rows from ENROLLMENT:

SELECT c.course_name, c.period, e.student_name
FROM course c, enrollment e
WHERE c.course_name = e.course_name(+)
  AND c.period = e.period(+);

In this query, ENROLLMENT is considered the optional table because (+) is appended to each of that table's columns. The following two queries accomplish the same join using the new, ANSI standard syntax:

SELECT c.course_name, c.period, e.student_name
FROM course c LEFT OUTER JOIN enrollment e
     ON c.course_name = e.course_name
        AND c.period = e.period;
 
SELECT c.course_name, c.period, e.student_name
FROM enrollment e RIGHT OUTER JOIN course c
     ON c.course_name = e.course_name
        AND c.period = e.period;

Note the difference between the two queries. The first query lists the COURSE table first, while the second query lists the ENROLLMENT table first. A LEFT OUTER JOIN makes the left table the required table. A RIGHT OUTER JOIN makes the right table the required table. In both queries, ENROLLMENT is the optional table, and COURSE is the required table.

Full outer joins

The full outer join represents a new capability in Oracle9i. A full outer join returns all rows from both tables. Where possible, rows from one table are matched with those from the other. In Oracle8i, you can simulate an outer join using a UNION query:

SELECT c.course_name, c.period, e.student_name
FROM course c, enrollment e
WHERE c.course_name = e.course_name(+)
  AND c.period = e.period(+)
UNION
SELECT e.course_name, e.period, e.student_name
FROM enrollment e
WHERE NOT EXISTS (
   SELECT *
   FROM course c2
   WHERE c2.course_name = e.course_name
     AND c2.period = e.period
   );

To execute this UNION query, Oracle needs to execute each SELECT statement separately and then combine the results. You potentially can end up with two full table scans for each table. In Oracle9i, you can use the FULL OUTER JOIN keyword to do the same thing:

SQL> SELECT c.course_name, c.period, e.student_name
2 FROM course c FULL OUTER JOIN enrollment e
3 ON c.course_name = e.course_name
4 AND c.period = e.period;
 
COURSE_NAME         PERIOD STUDENT_NAME
--------------- ---------- ---------------
English II               4 Michael
Spanish I                1 Billy
Spanish I                1 Jeff
English II               4 Jenny
Spanish 1                6
U.S. History             3    
                           Sky Lynn

As you can see, this query is easier to understand than the UNION query. It is also one SELECT statement and should execute more efficiently than the UNION query shown previously.

Specifying join conditions

Oracle9i supports three ways to specify join conditions: the ON, NATURAL, and USING clauses. The most general approach is to use the ON clause, in which you can specify any type of join condition. For example:

SELECT c.course_name, c.period, e.student_name
FROM course c FULL OUTER JOIN enrollment e
ON c.course_name = e.course_name
AND c.period = e.period;

This particular query is an equi-join; it looks for cases in which the corresponding columns from the two tables contain the same values. Because the names of the join columns are identical in the two tables, this join is also considered a natural join. Oracle supports the following shorthand syntax for natural joins:

SELECT course_name, period, e.student_name
FROM course c NATURAL FULL OUTER JOIN enrollment e;

Note that table aliases are not used for the join columns in the select list. In a natural join, Oracle recognizes only one version of each join column.

WARNING: Natural joins are dangerous! Use them only for queries you type in interactively.

While convenient, you must exercise caution when using the NATURAL join syntax. Consider what will happen if you code your programs using the NATURAL syntax and then later add a column, UPDATE_TIMESTAMP, to each of your tables. This column is automatically included in all your joins, and your join queries will return erroneous results. I strongly recommend the USING clause over the NATURAL keyword.

The USING clause represents another shortcut to performing an equi-join. The difference between USING and NATURAL is that with USING, you explicitly specify the join columns. Later changes to your tables won't alter the semantics of your queries. The following query uses USING to perform the same full outer join as in the previous two examples:

SQL> SELECT course_name, period, e.student_name
2 FROM course c FULL OUTER JOIN enrollment e
3 USING (course_name, period);
 
COURSE_NAME         PERIOD STUDENT_NAME
--------------- ---------- ---------------
English II               4 Michael
Spanish I                1 Billy
Spanish I                1 Jeff
English II               4 Jenny
Spanish 1                6
U.S. History             3
Spanish I                6 Sky Lynn

Compare the results of this query with the results shown previously in the section titled "Full outer joins." Notice that Sky Lynn's course name shows up in these results. That's because when USING or NATURAL is used, Oracle recognizes only one version of each join column and draws the value from whichever table it can.

Back to: Oracle SQL*Plus Pocket Reference, 2nd Edition


oreilly.com Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
International | About O'Reilly | Affiliated Companies | Privacy Policy

© 2001, O'Reilly & Associates, Inc.
webmaster@oreilly.com