Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Joining a Table with Itself: The Self-Join

A self-join compares values within a single table. You often use a self-join to unravel a complex relationship, such as which employees work for which managers or which parts belong in an assemblage. The critical elements of a self-join are

• Listing the table twice in the FROM clause and assigning a different alias to each

• Joining the table to itself, using the table aliases to qualify the columns

• Adding a condition (often an unequal join on another column) to prevent a row joining itself

The editors table uses the editor ID number in two ways: to identify an editor (ed_id) and to identify the editor's boss (ed_boss). Here's what the relevant data looks like:

SQL

select ed_id, ed_fname, ed_lname, ed_pos, ...