lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



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.
»