SQL: Visual QuickStart Guide

Creating a Self-Join

A self-join is a normal SQL join that joins a table to itself and retrieves rows from a table by comparing values in one or more columns in the same table. Self-joins often are used in tables with a reflexive relationship, which is a primary-key/foreign-key relationship from a column or combination of columns in a table to other columns in that same table. For information about keys, see “Primary Keys” and “Foreign Keys” in Chapter 2.

No tables in the sample database have a reflexive relationship, so I’ll show you an example that’s used in many SQL books and tutorials. Suppose that you have the following table, named employees:

 emp_id emp_name boss_id ------ ----------------- ------- E01 Lord Copper NULL E02 Jocelyn Hitchcock ...