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
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.
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.
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:
Identify the row in the
painting table that
contains the title “The Potato
Eaters,” so that you can refer to its