<< CROSS JOIN operation | Duplicates in UNION and INTERSECT >>

NATURAL JOIN operation

<< CROSS JOIN operation | Duplicates in UNION and INTERSECT >>

Derby Reference Manual

106

A CROSS JOIN operation can be replaced with an INNER JOIN where the join clause
always evaluates to true (for example, 1=1). It can also be replaced with a sub-query. So
equivalent queries would be:

SELECT * FROM CITIES LEFT OUTER JOIN
FLIGHTS INNER JOIN COUNTRIES ON 1=1
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'

SELECT * FROM CITIES LEFT OUTER JOIN
(SELECT * FROM FLIGHTS, COUNTRIES) S
ON CITIES.AIRPORT = S.ORIG_AIRPORT
WHERE S.COUNTRY_ISO_CODE = 'US'

NATURAL JOIN operation

A NATURAL JOIN is a

JOIN operation

that creates an implicit join clause for you based

on the common columns in the two tables being joined. Common columns are columns
that have the same name in both tables.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join.
The default is INNER join.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk
(*) in the select list, the asterisk will be expanded to the following list of columns (in this
order):

· All the common columns
· Every column in the first (left) table that is not a common column
· Every column in the second (right) table that is not a common column

An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to
every column of that table that is not a common column.

If a common column is referenced without being qualified by a table name, the column
reference points to the column in the first (left) table if the join is an INNER JOIN or a
LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common
column point to the column in the second (right) table.

Syntax

TableExpression

NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN {

TableViewOrFunctionExpression

| (

TableExpression

) }

Examples

If the tables COUNTRIES and CITIES have two common columns named COUNTRY
and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES

SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)

The following example is similar to the one above, but it also preserves unmatched rows
from the first (left) table:

SELECT * FROM COUNTRIES NATURAL LEFT JOIN CITIES

SQL queries