Practical 6: The Outer Join

Chapter Forty-Five

Syllabus topic Module 2, Practical 6: "Join Queries: Outer Join"

Pages 170 to 174 of 206

Aim

To write outer join queries.

What an inner join leaves out

An inner join keeps only the rows that matched. Three rows of this library never match anything, and each of them is a real fact somebody might want to know:

Ashwin Pajankar has written no book.
Discrete Maths has never been borrowed.
Vikram Rao has never borrowed anything.

An outer join keeps the unmatched rows of one side, filling the other side's columns with NULL.

LEFT JOIN

SELECT a.author_name, b.title
FROM author a
LEFT JOIN book b ON b.author_id = a.author_id
ORDER BY a.author_id, b.book_id;

+------------------+-------------------+
| author_name      | title             |
+------------------+-------------------+
| Dennis Ritchie   | The C Language    |
| Ramez Elmasri    | Database Systems  |
| Ramez Elmasri    | Discrete Maths    |
| Vikram Vaswani   | MySQL Reference   |
| Vikram Vaswani   | Learn SQL         |
| Behrouz Forouzan | Data Structures   |
| Behrouz Forouzan | Computer Networks |
| Behrouz Forouzan | Operating Systems |
| Ashwin Pajankar  | NULL              |
+------------------+-------------------+

Nine rows, where the inner join of [Practical 6: The Inner Join] gave eight. The extra row is Ashwin Pajankar, with NULL where a title would be.

A LEFT JOIN keeps every row of the LEFT table, the one named in the FROM, whether or not it matched. Where there was no match, every column taken from the right table is NULL.

LEFT OUTER JOIN is the full spelling and means exactly the same; the word OUTER is optional and carries no meaning of its own.

Finding the rows that did not match

This is the pattern worth memorising, because it answers a whole family of questions.

SELECT a.author_name
FROM author a
LEFT JOIN book b ON b.author_id = a.author_id
WHERE b.book_id IS NULL;

+-----------------+
| author_name     |
+-----------------+
| Ashwin Pajankar |
+-----------------+

Read it in two steps. The LEFT JOIN keeps every author, matched or not. The WHERE b.book_id IS NULL then keeps only the ones where nothing matched, because a matched row would have a real book_id there.

The column tested must be one that cannot be NULL in a matched row, which is why b.book_id is used and not b.price. A book with no price recorded would slip through a test on b.price.

The same shape answers the other two questions:

SELECT b.title AS never_borrowed
FROM book b
LEFT JOIN loan l ON l.book_id = b.book_id
WHERE l.loan_id IS NULL;

+-------------------+
| never_borrowed    |
+-------------------+
| Operating Systems |
| Discrete Maths    |
+-------------------+

SELECT m.member_name AS never_borrowed_anything
FROM member m
LEFT JOIN loan l ON l.member_id = m.member_id
WHERE l.loan_id IS NULL;

+-------------------------+
| never_borrowed_anything |
+-------------------------+
| Vikram Rao              |
+-------------------------+

Practical 6: The Outer Join

Counting, including the zeros

[Practical 6: The Inner Join] ended with a count per author that left out the author with no books. Now it can be done properly.

SELECT a.author_name, COUNT(b.book_id) AS books
FROM author a
LEFT JOIN book b ON b.author_id = a.author_id
GROUP BY a.author_id, a.author_name
ORDER BY books DESC, a.author_name;

+------------------+-------+
| author_name      | books |
+------------------+-------+
| Behrouz Forouzan |     3 |
| Ramez Elmasri    |     2 |
| Vikram Vaswani   |     2 |
| Dennis Ritchie   |     1 |
| Ashwin Pajankar  |     0 |
+------------------+-------+

Ashwin Pajankar is there with 0.

COUNT(b.book_id) and not COUNT(*). Here is why:

SELECT a.author_name, COUNT(*) AS wrong_count, COUNT(b.book_id) AS right_count
FROM author a
LEFT JOIN book b ON b.author_id = a.author_id
GROUP BY a.author_id, a.author_name
ORDER BY a.author_name LIMIT 2;

+------------------+-------------+-------------+
| author_name      | wrong_count | right_count |
+------------------+-------------+-------------+
| Ashwin Pajankar  |           1 |           0 |
| Behrouz Forouzan |           3 |           3 |
+------------------+-------------+-------------+

Ashwin Pajankar's group has one row in it, the row the LEFT JOIN manufactured with NULLs in it, so COUNT(*) counts that row and says 1. COUNT(b.book_id) skips it, because that column is NULL, and says 0.

That is the NULL rule of [Practical 4: Aggregate Functions, GROUP BY and HAVING] doing exactly what it promised, and it is the commonest wrong answer in this practical.

ON against WHERE, and why it matters here

On an inner join the two clauses are interchangeable. On an outer join they are not, and this is the hardest idea in the chapter.

SELECT a.author_name, b.title
FROM author a
LEFT JOIN book b ON b.author_id = a.author_id AND b.subject = 'Databases'
ORDER BY a.author_id, b.book_id;

+------------------+------------------+
| author_name      | title            |
+------------------+------------------+
| Dennis Ritchie   | NULL             |
| Ramez Elmasri    | Database Systems |
| Vikram Vaswani   | MySQL Reference  |
| Vikram Vaswani   | Learn SQL        |
| Behrouz Forouzan | NULL             |
| Ashwin Pajankar  | NULL             |
+------------------+------------------+

SELECT a.author_name, b.title
FROM author a
LEFT JOIN book b ON b.author_id = a.author_id
WHERE b.subject = 'Databases'
ORDER BY a.author_id, b.book_id;

+----------------+------------------+
| author_name    | title            |
+----------------+------------------+
| Ramez Elmasri  | Database Systems |
| Vikram Vaswani | MySQL Reference  |
| Vikram Vaswani | Learn SQL        |
+----------------+------------------+

Same tables, same condition, different answers.

In the ON, the condition decides what counts as a match. Authors with no Databases book are still kept, with NULL beside them, because the LEFT JOIN keeps every author whatever happens.

In the WHERE, the condition is applied after the join. The manufactured NULL rows have a NULL subject, NULL = 'Databases' is not true, and they are thrown away. The left join has been turned back into an inner join.

Practical 6: The Outer Join

The rule in one line: a condition on the right hand table of a LEFT JOIN belongs in the ON, not in the WHERE unless you actually meant to drop the unmatched rows.

RIGHT JOIN

SELECT b.title, a.author_name
FROM book b
RIGHT JOIN author a ON b.author_id = a.author_id
ORDER BY a.author_id, b.book_id;

+-------------------+------------------+
| title             | author_name      |
+-------------------+------------------+
| The C Language    | Dennis Ritchie   |
| Database Systems  | Ramez Elmasri    |
| Discrete Maths    | Ramez Elmasri    |
| MySQL Reference   | Vikram Vaswani   |
| Learn SQL         | Vikram Vaswani   |
| Data Structures   | Behrouz Forouzan |
| Computer Networks | Behrouz Forouzan |
| Operating Systems | Behrouz Forouzan |
| NULL              | Ashwin Pajankar  |
+-------------------+------------------+

A RIGHT JOIN keeps every row of the right table, the one named after the JOIN. The nine rows are the same nine as the LEFT JOIN at the top of this chapter, because the two tables have simply changed places.

Every RIGHT JOIN can be written as a LEFT JOIN by swapping the tables, and almost all real code does, because a query reads better when the table you care about is the first one named. Knowing that A RIGHT JOIN B equals B LEFT JOIN A is a fair viva question.

The FULL OUTER JOIN MySQL does not have

A full outer join keeps the unmatched rows of both sides. MySQL does not support it, and this is a real finding rather than an omission in this book:

SELECT a.author_name, b.title
FROM author a FULL OUTER JOIN book b ON b.author_id = a.author_id;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN book b ON b.author_id = a.author_id' at line 2

A syntax error. PostgreSQL and Oracle have it; MySQL does not.

The standard answer is a UNION of the two one-sided joins:

SELECT a.author_name, b.title
FROM author a LEFT JOIN book b ON b.author_id = a.author_id
UNION
SELECT a.author_name, b.title
FROM author a RIGHT JOIN book b ON b.author_id = a.author_id
ORDER BY author_name, title;

+------------------+-------------------+
| author_name      | title             |
+------------------+-------------------+
| Ashwin Pajankar  | NULL              |
| Behrouz Forouzan | Computer Networks |
| Behrouz Forouzan | Data Structures   |
| Behrouz Forouzan | Operating Systems |
| Dennis Ritchie   | The C Language    |
| Ramez Elmasri    | Database Systems  |
| Ramez Elmasri    | Discrete Maths    |
| Vikram Vaswani   | Learn SQL         |
| Vikram Vaswani   | MySQL Reference   |
+------------------+-------------------+

UNION stacks two results and removes the duplicates; UNION ALL keeps them and is faster where you know there are none. The two queries must have the same number of columns, in a compatible order.

Practical 6: The Outer Join

Here every book has an author, so the right hand side adds nothing new and the answer is the same nine rows. On a pair of tables with orphans on both sides it would be longer than either half, which is the point of it.

The four joins compared

Join	Keeps
`INNER JOIN`	only the rows that matched
`LEFT JOIN`	every row of the left table, matched or not
`RIGHT JOIN`	every row of the right table, matched or not
`FULL OUTER JOIN`	every row of both; not in MySQL, use a `UNION`
`CROSS JOIN`	every pair, with no condition at all

What beginners get wrong

Putting a condition on the right table in the WHERE. It silently turns the outer join back into an inner join.

COUNT(*) on an outer join. It counts the manufactured NULL row as 1. Count a column from the right table instead.

Testing a nullable column for the unmatched rows. Test the right table's primary key, which cannot be NULL in a matched row.

Expecting FULL OUTER JOIN to work in MySQL. It does not.

Reading a NULL in the result as missing data. In an outer join it means "no matching row", which is different: the data is not missing, the match is.

Swapping LEFT and RIGHT without swapping the tables. The answer changes completely.

Quick revision

LEFT JOIN keeps every row of the first table; unmatched rows get NULLs on the right.
RIGHT JOIN keeps every row of the second. A RIGHT JOIN B is B LEFT JOIN A.
OUTER is optional and adds nothing.
To find unmatched rows: LEFT JOIN then WHERE right.primary_key IS NULL.
Count with COUNT(right.col), never COUNT(*), or the empty groups come out as 1.
A condition on the right table belongs in the ON; in the WHERE it undoes the outer join.
MySQL has no FULL OUTER JOIN; use LEFT JOIN UNION RIGHT JOIN.
UNION removes duplicates, UNION ALL keeps them.

What goes in your journal

Aim, then the inner join and the left join over the same two tables one under the other, so the extra row and its NULLs are visible. Then the unmatched-rows pattern, then the count with COUNT(col) beside the same count with COUNT(*).

That last pair, 0 against 1 for the author with no books, is the single most useful thing in this chapter to have written down before a viva.

Test yourself

1. What does a LEFT JOIN keep that an INNER JOIN does not? Every row of the left table that had no match, with NULLs in the columns taken from the right table.

Practical 6: The Outer Join

2. How do you list the rows of one table that have no match in another? LEFT JOIN the second table and then WHERE second.primary_key IS NULL.

3. Why must that test use the primary key rather than any column? Because a matched row could itself contain a NULL in an ordinary column, and would then be reported as unmatched.

4. Why does COUNT() give 1 for a group with no matching rows? Because the LEFT JOIN produced one row for that group, filled with NULLs, and COUNT() counts rows regardless of their contents.

5. What happens if a condition on the right table is put in the WHERE instead of the ON? The manufactured NULL rows fail the condition and are removed, so the outer join behaves as an inner join.

6. Does MySQL support FULL OUTER JOIN? No. The same result is obtained with a UNION of a LEFT JOIN and a RIGHT JOIN.

7. Rewrite A RIGHT JOIN B ON ... as a left join. B LEFT JOIN A ON ..., with the same condition.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Major Practical 1

Report or request