O'Reilly logo

Practical SQL Handbook, The: Using SQL Variants, Fourth Edition by Sandra L. Emerson, Marcy Darnovsky, Judith S. Bowman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required