MySQL Cookbook

Comparing a Table to Itself

Problem

You want to compare records in a table to other records in the same table. For example, you want to find all paintings in your collection by the artist who painted “The Potato Eaters.” Or you want to know which states listed in the states table joined the Union in the same year as New York. Or you want to know which of the people listed in the profile table have some favorite food in common.

Solution

Problems that require comparing a table to itself involve an operation known as a self-join. It’s much like other joins, except that you must always use table aliases so that you can refer to the same table different ways within the query.

Discussion

A special case of joining one table to another occurs when both tables are the same. This is called a self-join. Although many people find the idea confusing or strange to think about at first, it’s perfectly legal. Be assured that you’ll get used to the concept, and more than likely will find yourself using self-joins quite often because they are so important.

A tip-off that you need a self-join is when you want to know which pairs of elements in a table satisfy some condition. For example, suppose your favorite painting is “The Potato Eaters” and you want to identify all the items in your collection that were done by the artist who painted it. You can do so as follows:

1. Identify the row in the painting table that contains the title “The Potato Eaters,” so that you can refer to its a_id value.

2. Use the