MySQL Cookbook

Finding Rows with No Match in Another Table

Problem

You want to find rows in one table that have no match in another. Or you want to produce a list on the basis of a join between tables, but you want the list to include an entry even when there are no matches in the second table.

Solution

Use a LEFT JOIN. As of MySQL 3.23.25, you can also use a RIGHT JOIN.

Discussion

The preceding sections focused on finding matches between two tables. But the answers to some questions require determining which records do not have a match (or, stated another way, which records have values that are missing from the other table). For example, you might want to know which artists in the artist table you don’t yet have any paintings by. The same kind of question occurs in other contexts, such as:

• You’re working in sales. You have a list of potential customers, and another list of people who have placed orders. To focus your efforts on people who are not yet actual customers, you want to find people in the first list that are not in the second.

• You have one list of baseball players, another list of players who have hit home runs, and you want to know which players in the first list have not hit a home run. The answer is determined by finding those players in the first list who are not in the second.

For these types of questions, you need to use a LEFT JOIN.

To see why, let’s determine which artists in the artist table are missing from the painting table. At present, the tables are small, so it’s easy to ...