Other Join Types in Oracle

So far we have used inner joins (by far the most common) and outer joins. We finish this section with a few less well known, but occasionally useful, types of join.

Self-Join

A self-join joins a table to itself.

Suppose we wish to find countries that are the same size or larger than New Zealand.

SELECT ctry.ctry_name, ctry.ctry_area, nz.ctry_area AS nz_area
FROM country nz
  JOIN country ctry ON ctry.ctry_area >= nz.ctry_area
                    AND ctry.ctry_id != nz.ctry_id
WHERE nz.ctry_name = 'New Zealand';

CTRY_NAME	CTRY_AREA	NZ_AREA
Germany	137847	104454
Australia	2966200	104454

The COUNTRY table is joined to itself and table aliases are used to qualify the columns. The ctry.ctry_id != nz.ctry_id condition is added to the query to exclude New Zealand from the result set.

Cross Join

Cross joins take all the rows from one table and join them to all the rows of another table, without qualification. A cross join is also known as a Cartesian product.

Suppose we need to produce a report to act as a manual checklist to record which continents each head of state will be visiting in the coming year. The list must show all possible continent / head of state combinations.

SELECT hos_name, cont_name, ' ' AS "VISITING (PLEASE TICK)"
FROM continent
  CROSS JOIN head_of_state
ORDER BY hos_name, cont_name;

HOS_NAME	CONT_NAME	VISITING (PLEASE TICK)
President Horst Köhler	Africa
President Horst Köhler	Antarctica
President Horst Köhler	Asia
President Horst Köhler	Europe
President Horst Köhler	North America
President Horst Köhler	Oceania
President Horst Köhler	South America
President John Atta Mills	Africa
President John Atta Mills	Antarctica
President John Atta Mills	Asia
President John Atta Mills	Europe
President John Atta Mills	North America
President John Atta Mills	Oceania
President John Atta Mills	South America
President Karolos Papoulias	Africa
President Karolos Papoulias	Antarctica
President Karolos Papoulias	Asia
President Karolos Papoulias	Europe
President Karolos Papoulias	North America
President Karolos Papoulias	Oceania
President Karolos Papoulias	South America
President Mikheil Saakashvili	Africa
President Mikheil Saakashvili	Antarctica
President Mikheil Saakashvili	Asia
President Mikheil Saakashvili	Europe
President Mikheil Saakashvili	North America
President Mikheil Saakashvili	Oceania
President Mikheil Saakashvili	South America
President Yahya Jammeh	Africa
President Yahya Jammeh	Antarctica
President Yahya Jammeh	Asia
President Yahya Jammeh	Europe
President Yahya Jammeh	North America
President Yahya Jammeh	Oceania
President Yahya Jammeh	South America
President-elect Ali Bongo Ondimba	Africa
President-elect Ali Bongo Ondimba	Antarctica
President-elect Ali Bongo Ondimba	Asia
President-elect Ali Bongo Ondimba	Europe
President-elect Ali Bongo Ondimba	North America
President-elect Ali Bongo Ondimba	Oceania
President-elect Ali Bongo Ondimba	South America

Theta-Style Joins

You may come across an older, more traditional style of join called a theta-style join. Technically, it is a cartesian product (see above) with the join condition specified in the where clause.

SELECT ctry_name, cont_name
FROM continent cont, country ctry
WHERE cont.cont_id = ctry.cont_id;

CTRY_NAME	CONT_NAME
Australia	Oceania
Gabon	Africa
Gambia	Africa
Georgia	Europe
Germany	Europe
Ghana	Africa
Greece	Europe
New Zealand	Oceania

A comma separated list of tables in the FROM clause is equivalent to the cross join shown in the previous example. However, the combination of rows returned is limited to the ones which satisfy the join condition in the WHERE clause.

Once upon a time all queries where written this way. However, the ANSI join syntax we have been using so far is much more preferable because it provides a clean separation between join conditions (in the FROM clause) and the non-join selection criteria (in the WHERE clause).

For example, these two statements are equivalent but the second is tidier and easier to understand.

Theta-Style

SELECT ctry_name, city_name
FROM continent cont, country ctry, city
WHERE cont.cont_id = ctry.cont_id
  AND ctry.ctry_id = city.ctry_id
  AND ctry_area > 10000
  AND cont_name = 'Oceania';

ANSI-Style

SELECT ctry_name, city_name
FROM continent
  JOIN country USING (cont_id)
  JOIN city USING (ctry_id)
WHERE ctry_area > 10000
  AND cont_name = 'Oceania';

Natural Join

Last (and probably least!) we have the natural join. A natural join will automatically join two tables based on columns with the same name.

SELECT ctry_name, cont_name
FROM country
  NATURAL JOIN continent;

CTRY_NAME	CONT_NAME
Australia	Oceania
Gabon	Africa
Gambia	Africa
Georgia	Europe
Germany	Europe
Ghana	Africa
Greece	Europe
New Zealand	Oceania

This may seem like a useful feature but is an accident waiting to happen if used in a production environment.

For example, suppose we have this query:

SELECT city_name, city_area
FROM country
  NATURAL JOIN city
WHERE ctry_name='Greece';

CITY_NAME	CITY_AREA
Athens	15
Thessaloniki	7
Piraeus	4
Patras	48
Heraklion	42

No problem. The join is matching CONT_ID on the CONTINENT table to CONT_ID on the COUNTRY table (which is equivalent to 'FROM continent JOIN country USING (CONT_ID)' ). Now suppose the DBA changes the database as follows:

ALTER TABLE city ADD pop_upd_on DATE;

Now if we re-run the original query:

SELECT city_name, city_area
FROM country
  NATURAL JOIN city
WHERE ctry_name='Greece';

-- 0 rows selected

The query has broken!

The natural join is now joining on CONT_ID and POP_UPD_ON (which is equivalent to 'FROM continent JOIN country USING (CONT_ID, POP_UPD_ON)' ).

For this reason, natural joins should never be used in a production environment.

quick start