Oracle Outer Joins

An outer join returns rows from one or both of the tables even if they do not match the join condition.

There are three types of outer join.

Left outer join
Right outer join
Full outer join

A Simple Outer Join

Let's dive straight into an example of the most common type of outer join, a left outer join.

SELECT cont_name, ctry_name
FROM continent
  LEFT OUTER JOIN country USING (cont_id);

CONT_NAME	CTRY_NAME
Africa	Ghana
Africa	Gabon
Africa	Gambia
Asia	(null)
Europe	Georgia
Europe	Germany
Europe	Greece
North America	(null)
South America	(null)
Oceania	Australia
Oceania	New Zealand
Oceania	(null)

All of the continents are displayed, even if there are no corresponding rows in the COUNTRY table. This is in contrast to the behavior we saw earlier with an inner join, which would exclude such rows.

The LEFT keyword indicates that all rows are returned from the table on the left of the join, in this case CONTINENT. Columns values from the table on the right are replaced with nulls where there is no corresponding row.

The OUTER keyword is optional for all outer join types (right, left and full), so the previous statement could have been written as:

SELECT cont_name, ctry_name
FROM continent
  LEFT JOIN country USING (cont_id);

The World Schema

To properly demonstrate outer joins we will add a couple more tables to our schema.

Schema diagram for world model

The HEAD_OF_STATE and VISIT tables complete the schema introduced at the start of this section.

SELECT *
FROM head_of_state;

HOS_ID	CTRY_ID	HOS_NAME
1	122	President Mikheil Saakashvili
2	14	President Horst Köhler
3	73	President Karolos Papoulias
4	147	President Yahya Jammeh
5	149	President-elect Ali Bongo Ondimba

SELECT *
FROM visit;

CTRY_ID	HOS_ID
14	3
48	3
73	1
73	2
149	3

The HEAD_OF_STATE table is related to COUNTRY in two different ways; a one-to-one relationship to determine which country the head of state represents and a many-to-many relationship to determine which countries a head of state has visited.

The many-to-many relationship relies on the VISIT table to store combinations of primary key values from COUNTRY and CONTINENT when a visit has taken place.

For example, we can find which countries President Karolos Papoulias has visited using these inner joins.

SELECT ctry_name
FROM head_of_state
  INNER JOIN visit USING (hos_id)
  INNER JOIN country ctry ON ctry.ctry_id = visit.ctry_id
WHERE hos_name = 'President Karolos Papoulias';

CTRY_NAME
Germany
Ghana
Gabon

Or, conversely, which heads of state have visited Greece.

SELECT hos_name
FROM head_of_state
  INNER JOIN visit USING (hos_id)
  INNER JOIN country ctry ON ctry.ctry_id = visit.ctry_id
WHERE ctry_name = 'Greece';

HOS_NAME
President Mikheil Saakashvili
President Horst Köhler

Left Outer Join

But suppose we wish to show all countries and their visitors, even the ones that have not yet been visited by a head of state. We can use a left outer join to achieve this:

SELECT ctry_name, hos_name
FROM country
  LEFT OUTER JOIN visit USING (ctry_id)
  LEFT OUTER JOIN head_of_state USING (hos_id);

CTRY_NAME	HOS_NAME
Australia	(null)
Gabon	President Karolos Papoulias
Gambia	(null)
Georgia	(null)
Germany	President Karolos Papoulias
Ghana	President Karolos Papoulias
Greece	President Horst Köhler
Greece	President Mikheil Saakashvili
New Zealand	(null)

Australia, Gambia, Georgia and New Zealand still appear in the list even though they have had no visitors and therefore have no entries in the VISIT table.

Right Outer Join

We can easily switch this around to display all the heads of state even if they have not visited any countries.

SELECT ctry_name, hos_name
FROM country
  RIGHT OUTER JOIN visit USING (ctry_id)
  RIGHT OUTER JOIN head_of_state USING (hos_id);

CTRY_NAME	HOS_NAME
Greece	President Horst Köhler
(null)	President John Atta Mills
Gabon	President Karolos Papoulias
Germany	President Karolos Papoulias
Ghana	President Karolos Papoulias
Greece	President Mikheil Saakashvili
(null)	President Yahya Jammeh
(null)	President-elect Ali Bongo Ondimba

Using a RIGHT OUTER JOIN we tell Oracle that all rows should be returned from the table on the right. Clearly, unlike an inner join, the ordering of the tables in the FROM clause is significant.

Full Outer Join

And finally, let's include all rows from both sides of the join. For this we will need to do a full outer join.

SELECT ctry_name, hos_name
FROM country
  FULL OUTER JOIN visit USING (ctry_id)
  FULL OUTER JOIN head_of_state USING (hos_id);

CTRY_NAME	HOS_NAME
Australia	(null)
Gabon	President Karolos Papoulias
Gambia	(null)
Georgia	(null)
Germany	President Karolos Papoulias
Ghana	President Karolos Papoulias
Greece	President Mikheil Saakashvili
Greece	President Horst Köhler
New Zealand	(null)
(null)	President John Atta Mills
(null)	President-elect Ali Bongo Ondimba
(null)	President Yahya Jammeh

All rows are returned at least once with nulls in the columns where a corresponding row does not exist.

Additional Selection Criteria

Care must be taken when including additional selection criteria with outer joins. Let's see what happens if we try to exclude President Köhler from the results of our last query using this method:

SELECT ctry_name, hos_name
FROM country
  LEFT OUTER JOIN visit USING (ctry_id)
  LEFT OUTER JOIN head_of_state USING (hos_id)
WHERE hos_name != 'President Horst Köhler';

CTRY_NAME	HOS_NAME
Gabon	President Karolos Papoulias
Germany	President Karolos Papoulias
Ghana	President Karolos Papoulias
Greece	President Mikheil Saakashvili

Countries with no visitors are missing again. This is the correct behavior because these countries will return a null value in the HOS_NAME and, as we know, comparisons using = and != with null do not return rows.

The solution, in this case, is to add an extra IS NULL condition to the WHERE clause.

SELECT ctry_name, hos_name
FROM country
  LEFT OUTER JOIN visit USING (ctry_id)
  LEFT OUTER JOIN head_of_state USING (hos_id)
WHERE hos_name != 'President Horst Köhler'
  OR hos_name IS NULL;

CTRY_NAME	HOS_NAME
Australia	(null)
Gabon	President Karolos Papoulias
Gambia	(null)
Georgia	(null)
Germany	President Karolos Papoulias
Ghana	President Karolos Papoulias
Greece	President Mikheil Saakashvili
New Zealand	(null)

This time President Köhler is excluded and all countries are displayed as intended.

This is also the reason for the outer join between VISIT and HEAD_OF_STATE in the previous query. If a VISIT row does not exist for a COUNTRY then a null is returned in the HOS_ID and no rows will be matched from the HEAD_OF_STATE table if an inner join is used. However, a left outer join will work because all rows form the left hand side of the join are always returned.

NVL Function

Finally, the NVL funtion can be used to replace null values with something more readable.

SELECT
  nvl(ctry_name, '(No official visits)') country,
  nvl(hos_name, '(Not visited)') head_of_state
FROM country
  FULL OUTER JOIN visit USING (ctry_id)
  FULL OUTER JOIN head_of_state USING (hos_id);

CTRY_NAME	HOS_NAME
Australia	(Not visited)
Gabon	President Karolos Papoulias
Gambia	(Not visited)
Georgia	(Not visited)
Germany	President Karolos Papoulias
Ghana	President Karolos Papoulias
Greece	President Mikheil Saakashvili
Greece	President Horst Köhler
New Zealand	(Not visited)
(No official visits)	President John Atta Mills
(No official visits)	President-elect Ali Bongo Ondimba
(No official visits)	President Yahya Jammeh

other join types