Соединение таблицы с самой собой

Столбец referred_by в customers хранит идентификатор клиента, который порекомендовал магазин. Это такой же внешний ключ, как любой другой, только он указывает на ту же таблицу: один клиент ссылается на другого. Чтобы увидеть, кто кому порекомендовал магазин, нужны две строки customers рядом: клиент и тот, кто его привёл. Поэтому customers соединяют с самой собой:

SELECT ref.name AS referrer, c.name AS customer
FROM customers AS c
JOIN customers AS ref ON c.referred_by = ref.id
ORDER BY ref.name, c.name;

Самосоединение использует одну и ту же таблицу дважды, под двумя псевдонимами, и база данных считает их двумя отдельными таблицами, у которых просто совпадают строки. c — это клиент, ref — тот, кто его привёл, а ON сопоставляет каждого клиента со строкой, чей id равен его referred_by. Здесь псевдонимы не просто короче: это единственный способ сказать, какую из двух копий вы имеете в виду, ведь customers.name может относиться к любой из них. Давайте им имена, которые говорят, какую роль играет каждая копия.

Всё остальное работает как в любом соединении. Клиенты, у которых referred_by равен NULL, не нашли пары, поэтому JOIN их не включил; LEFT JOIN сохранил бы их. А GROUP BY может подсчитать, например, сколько людей привёл в магазин каждый клиент.

Таблицу можно соединить с самой собой и по условию, которое не является внешним ключом. Какие игры относятся к одному жанру? Сопоставьте каждую игру с другими играми, у которых тот же genre:

SELECT a.title, b.title AS same_genre
FROM games AS a
JOIN games AS b ON a.genre = b.genre AND a.id < b.id
WHERE a.genre = 'Strategy'
ORDER BY a.title, b.title;

a.id < b.id (в ON может быть несколько условий, соединённых через AND) оставляет каждую пару только один раз и никогда не сопоставляет игру с ней самой. Без этого условия вы получили бы Castle Siege с Castle Siege, а также и Castle Siege с Tiny Kingdoms, и Tiny Kingdoms с Castle Siege.