12.4. Comparing a Table to Itself

12.4.1. Problem

You want to compare rows in a table to other rows 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 states did not join the Union in the same year as any other state.

12.4.2. Solution

Problems that require comparing a table to itself involve an operation known as a self-join. It’s performed 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 statement.