Joining a table to itself

How JOIN works

SELECT a.title AS first, b.title AS second
FROM books a JOIN books b
  ON a.author = b.author;

• What is a self-join?

  A table joined to itself: FROM books a JOIN books b. It compares rows of the same table with each other, such as books by the same author.

• Why the short names?

  Both copies are called books, so each needs its own name (an alias). Then a.title and b.title say which copy you mean.

• Why does a book pair with itself?

  A book has the same author as itself, so a.author = b.author matches it. And Emma pairs with Pride and Prejudice both ways round.

• How do I keep each pair once?

  Add AND a.year < b.year (or a.book_id < b.book_id). Only one order passes, and a row is never less than itself.

• How is this like Excel?

  There's no easy Excel version: you'd need a lookup of the table against itself, or a PivotTable with author in Rows and title twice.

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.