Outer Join

An inner join keeps only the rows that found a match in the other table. An outer join works differently: it always returns every row of one table or of both, filling the missing half with NULL.

There are three kinds of outer join: left (LEFT), right (RIGHT) and full (FULL). The kind is mandatory — a bare OUTER JOIN is a syntax error. The word OUTER itself is optional: LEFT JOIN and LEFT OUTER JOIN mean exactly the same, and the shorter form is used below.

LEFT OUTER JOIN

Returns every row of the left table. If a row finds a match in the right table, the two rows are glued together; if it does not, the right table columns are filled with NULL.

For example, let's get the schedule of calls from the database, joined with the corresponding classes in the schedule.

Data in the Timepair table (schedule of calls):

Data in the Schedule table (schedule of classes):

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

All eight calls made it into the result, exactly as a left join promises. But the result has 43 rows, not 8.

A join does not supplement the left table — it goes through every matching pair of rows. The same pair number appears in the schedule many times, on different days and for different classes, and every match produces its own row. When the key is not unique in the right table, the result has more rows than the left table.

At the end of the result there are rows where every class column is NULL. These are the calls with no class at all: there is no match, but a row of the left table is guaranteed to appear in the result.

Rows without a match

Those NULL values are the basis of the most common practical trick — finding records that have no match. It is enough to keep only the rows where the key of the right table is empty:

MySQL 8.1
SELECT Timepair.id, start_pair, end_pair
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id
WHERE Schedule.number_pair IS NULL;

Three calls are left — the ones with no class scheduled.

A join from which only the rows without a match are kept is called an anti join (ANTI JOIN). It has no operator of its own: both in MySQL and in PostgreSQL it is written exactly like this — a join plus an IS NULL condition.

RIGHT OUTER JOIN

The mirror image of the left join: every row of the right table is guaranteed to appear in the result, and the missing columns of the left table are filled with NULL.

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    RIGHT JOIN Schedule ON Schedule.number_pair = Timepair.id;

The result has 40 rows — exactly as many as there are records in Schedule — and not a single row with NULL. In other words, it matches the inner join completely.

That happened because every class refers to an existing call: the right table simply has no unmatched rows. The kind of join sets the rule, but what ends up in the result is decided by the data.

FULL OUTER JOIN

Returns every row of both tables. Rows that found a match are glued together, while unmatched rows of the left and right tables appear in the result with NULL instead of the missing half.

The result of a full join is made of three parts:

• the rows of the inner join (INNER JOIN);
• the rows of the left table that found no match;
• the rows of the right table that found no match.

MySQL 8.1
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    FULL OUTER JOIN Schedule ON Schedule.number_pair = Timepair.id;

MySQL 8.1
SELECT table_fields
FROM left_table
    LEFT JOIN right_table ON right_table.key = left_table.key

UNION ALL

SELECT table_fields
FROM left_table
    RIGHT JOIN right_table ON right_table.key = left_table.key
WHERE left_table.key IS NULL;

All types of table joins

SELECT table_fields
FROM left_table
    LEFT JOIN right_table ON right_table.key = left_table.key

Let's check yourself. The left table has 8 rows. How many rows will a LEFT JOIN with the right table return?