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;
|
It is a common practice to use table
aliases
when selecting data from multiple tables. Whenever there is an
ambiguity in the column names, you must use a table alias (or the
table name) to qualify any ambiguous column name. For example:
SELECT d.dept_id, d.name, l.regional_group
FROM department d JOIN location l
ON d.location_id = l.location_id;
In this example, the column name location_id
appears in both the tables. Therefore, the table aliases
d and l are used in the ON
clause to ask Oracle to equate the location_id
column from the department table with the
location_id column from the
location table. The table aliases have been used
with the columns in the SELECT clause as well, even though those
column names are unambiguous. It is a good practice to use table
aliases everywhere in a query if you are using them at all.
|
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.
