3.2 Join Conditions

Usually, when you write a join query, you specify a condition that conveys a relationship between the tables specified in the FROM clause. This condition is referred to as the join condition. The join condition specifies how the rows from one table will be combined with the rows of another table. This join condition is usually applied to the foreign key columns of one table and the primary or unique key columns of another table. In the previous example, the ON clause specifies the join condition by which the location_id column of the department table is equated with the location_id column of the location table:

ON department.location_id = location.location_id;

To perform the join, Oracle picks up one combination of rows from the two tables, and checks to see whether the join condition is true. If the join condition is true, Oracle includes this combination of rows in the result set. This process is repeated for all combinations of rows from the two tables. Some of the things that you should know about join conditions are discussed in the following list:

• The columns specified in a join condition need not be specified in the SELECT list. In the following example, the join condition involves the location_id column from the department and location tables; however, the location_id column is not selected:

  SELECT d.dept_id, d.name, l.regional_group
  
  FROM department d JOIN location l
  
  ON d.location_id = l.location_id;

  
  

SELECT d.dept_id, d.name, l.regional_group

FROM department d JOIN location l

ON d.location_id = l.location_id;

• Usually a join condition is specified on the foreign key columns of one table and the primary key or unique key columns of another table. However, you can join on other columns as well. A join condition involves columns that relate two tables in some logical way.

• A join condition may involve more than one column. This is usually the case when a foreign key constraint consists of multiple columns.

• The total number of join conditions in a query is always equal to the total number of tables less one.

• The data types of the columns involved in a join condition need to be compatible, but not necessarily the same. Oracle performs implicit data type conversion between the join columns, if required.

• It is not necessary that a join condition involve the equal-to (=) operator. A join condition may contain other operators as well. Joins involving other operators are discussed later in this chapter in Section 3.3.4.

3.2.1 The USING Clause

In this chapter's first example, the join condition was specified in the ON clause, which contained an expression defining the relationship between the two tables. Specifying the join condition can be simplified if the following conditions hold true:

• The join depends on an equality condition between two columns, or between sets of two columns, to relate the rows from the two tables.

• The names of the join columns are identical in both the tables.

If these two conditions are satisfied, you can apply the USING clause to specify the join condition. Earlier, you saw the following example of a join query:

SELECT department.location_id, department.name, location.regional_group

FROM department JOIN location

ON department.location_id = location.location_id;

The column involved in the join condition (location_id) is named identically in both the tables, and its value must be the same in both tables. Therefore, this join query can be rewritten as:

SELECT location_id, department.name, location.regional_group

FROM department JOIN location

USING (location_id);

The USING clause affects the semantics of the SELECT clause. The USING clause tells Oracle that the tables in the join have identical names for the column in the USING clause. Oracle then merges those two columns, and recognizes only one such column with the given name. If you include a join column in the SELECT list, Oracle doesn't allow you to qualify that column with a table name (or table alias). If you attempt to qualify a join column name in the SELECT list using either an alias or a table name, you will get an error:

SELECT department.location_id, department.name, location.regional_group

FROM department JOIN location

USING (location_id);

SELECT department.location_id, department.name, location.regional_group

       *

ERROR at line 1:

ORA-25154: column part of USING clause cannot have qualifier

This is why our USING query did not alias the location_id column in the SELECT list.

3.2.2 Conditions Involving Multiple Columns

Quite often you will encounter a join condition that involves multiple columns from each table. If a join condition consists of multiple columns, you need to specify all the predicates in the ON clause. For example, if tables A and B are joined based on columns c1 and c2, the join condition would be:

SELECT  . . . 

FROM A 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 JOIN B 

USING (c1, c2);

3.2.3 The Natural Join Clause

A natural join between two tables relates the rows from the two tables based on all pairs of columns, one column from each table, with matching names. You don't specify a join condition. The following example illustrates a natural join:

SELECT department.name, location.regional_group

FROM department NATURAL JOIN location;



NAME                 REGIONAL_GROUP

-------------------- ---------------

ACCOUNTING           NEW YORK

RESEARCH             DALLAS

OPERATIONS           BOSTON

In this example, the two tables?department and location?have the same name for the column location_id. Therefore, the join takes place by equating the location_id from the department table to the location_id from the location table. The preceding query is equivalent to the following queries:

SELECT department.name, location.regional_group

FROM department JOIN location

ON department.location_id = location.location_id;



SELECT department.name, location.regional_group

FROM department JOIN location

USING (location_id);

While using a natural join, you are not allowed to qualify the common columns with table names or aliases (similar to the effect of the USING clause). For example, if you want to include the location_id column in the SELECT list, and you specify department.location_id, you will get an error:

SELECT department.location_id, department.name, location.regional_group

FROM department NATURAL JOIN location;

SELECT department.location_id, department.name, location.regional_group

       *

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier

You need to remove the department qualifier so the location_id column can include it in the SELECT list:

SELECT location_id, department.name, location.regional_group

FROM department NATURAL JOIN location;



LOCATION_ID NAME                 REGIONAL_GROUP

----------- -------------------- ---------------

        122 ACCOUNTING           NEW YORK

        124 RESEARCH             DALLAS

        167 OPERATIONS           BOSTON

Implicit specification of join conditions can have some unwanted side affects. Let's take the example of join between the supplier and part tables to illustrate this:

DESC supplier

 Name                                      Null?    Type

 ----------------------------------------- -------- --------------

 SUPPLIER_ID                               NOT NULL NUMBER(5)

 NAME                                      NOT NULL VARCHAR2(30)



DESC part

 Name                                      Null?    Type

 ----------------------------------------- -------- --------------

 PART_NBR                                  NOT NULL VARCHAR2(20)

 NAME                                      NOT NULL VARCHAR2(30)

 SUPPLIER_ID                               NOT NULL NUMBER(5)

 STATUS                                    NOT NULL VARCHAR2(20)

 INVENTORY_QTY                                      NUMBER(6)

 UNIT_COST                                          NUMBER(8,2)

 RESUPPLY_DATE                                      DATE

An inner join between these two tables, generates the following result:

SELECT supplier.supplier_id, part.part_nbr

FROM supplier JOIN part

ON supplier.supplier_id = part.supplier_id;



SUPPLIER_ID PART_NBR

----------- -----------

          1 AI5-4557

          2 TZ50828

          3 EI-T5-001

The following example illustrates a natural join between these two tables:

SELECT supplier_id, part.part_nbr

FROM supplier NATURAL JOIN part;



no rows selected

No output. What happened? The reason lies in the fact that, aside from supplier_id, these two tables have another pair of columns with a common name. That column is name. So, when you ask for a natural join between the supplier and the part tables, the join takes place not only by equating the supplier_id column of the two tables, but the name column from the two tables is equated as well. Since, no supplier name is the same as a part name from that same supplier, no rows are returned by the query. The equivalent inner join of the preceding natural join is:

SELECT supplier.supplier_id, part.part_nbr

FROM supplier JOIN part

ON supplier.supplier_id = part.supplier_id

AND supplier.name = part.name;

or, expressed via the USING clause:

SELECT supplier_id, part.part_nbr

FROM supplier JOIN part

USING (supplier_id, name);

By looking at the inner join queries we've just presented, you can very well understand why the natural join between the supplier and part tables didn't return any rows. You must be aware of this potential for error when using natural joins. To avoid such problems, we recommend explicitly specifying join conditions, using either the ON or the USING clauses.