INNER JOINs

An INNER JOIN produces a result table containing composite rows created by combining rows from two tables where some pre-defined, or explicitly specified, join condition evaluates to true.

Rows that do not satisfy the JOIN condition will not appear in the result table of an INNER JOIN.

The INNER JOIN is the default JOIN type, therefore the keyword INNER is optional and may be omitted.

NATURAL JOIN

The result table of a NATURAL JOIN contains one row for each case where all the common columns in the two tables contain values that are equal.

Common columns are those which have the same name in each table. The common columns must have a data type that allows values in the respective tables to be compared.

A row in the result table contains the combined set of columns from each table, except that the common columns appear only once. The common columns appear first (at the left of the table) followed by the remaining columns from table-reference-1, followed by those from table-reference-2.

Syntax

The syntax for a NATURAL JOIN is:

If there are no rows where all the common columns have equal values, the result table is an empty table (i.e. it has a set of columns as just described, but the number of rows is zero).

For example, two tables contain different sets of information on people, and each table has a FIRST_NAME column and a SURNAME column to identify the person to whom the information applies.

When both the FIRST_NAME column and the SURNAME column contain the same values in a row in each table, it means those rows are related. A NATURAL JOIN between these two tables would produce a result table with a single composite row for each person, containing all the information held in both tables, with the SURNAME and FIRST_NAME columns appearing once in the rows of the result.

Note: It is actually possible to perform a NATURAL JOIN between two tables which have no common columns at all. In this case the result table is the Cartesian product (sometimes called the CROSS JOIN) of the two tables.

Examples

The following example combines a natural join with a scalar subquery (for an explanation of scalar subqueries, see Select Specification).

The NATURAL INNER JOIN construction below will join the employees and salaries tables on all columns which share the same name.

EMPLOYEES NATURAL INNER JOIN SALARIES

JOIN USING

JOIN USING allows a list of column names to be specified. This kind of join is conceptually the same as a NATURAL JOIN except that the join is based on the specified columns rather than on all the common columns.

Specifying the columns explicitly instead of using the entire set of common columns is useful in situations where some of the common columns may not contain identical values even though the respective rows are related (e.g. as in a REMARKS column).

The columns specified in the list must be common to both tables, they must be specified in an unqualified manner and must have a data type that allows the values in the respective tables to be compared.

A row in the result table contains the combined set of columns from each table, except that the common columns appear only once. The columns specified in the list of column names appear first (at the left of the table) followed by the remaining columns from table-reference-1, followed by those from table-reference-2.

The syntax for JOIN USING is:

Example

The construction below will make a join on the specified columns.

EMPLOYEES INNER JOIN SALARIES USING (EMP_NO)

JOIN ON

JOIN ON allows a join condition to be specified. The result table of this kind of join is produced by applying the specified join condition to the Cartesian product of the two tables. The result table will contain only those rows for which the join condition evaluates to true.

The join condition cannot reference common columns unless they are qualified (e.g. by table name).

A row in the result table contains the combined set of columns from each table. The columns from table-reference-1 appear first followed by those from table-reference-2. Common columns will therefore appear twice.

The syntax for JOIN ON is:

Example

The ON condition limits the number of rows in the join to those whose salaries are less than 10 000.