Joining a Table to Itself

The column referred_by in customers holds the ID of the customer who recommended the shop. It's a foreign key like any other, except that it points at the same table: one customer points at another. To see who recommended the shop to whom, you need two rows of customers side by side, the customer and the one who referred them. So you join customers with itself:

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;

A self-join uses the same table twice, under two aliases, and the database treats them as two separate tables that happen to have the same rows. c is the customer, ref the one who referred them, and ON pairs each customer with the row whose id is their referred_by. Here, the aliases aren't just shorter: they're the only way to say which of the two copies you mean, because customers.name could be either. Give them names that say which role each copy plays.

Everything else works as in any join. The customers whose referred_by is NULL found no match, so JOIN left them out; LEFT JOIN would keep them. And GROUP BY can count, for example, how many people each customer has brought to the shop.

A table can also be joined with itself on a condition other than a foreign key. Which games are in the same genre? Pair each game with the other games whose genre is the same:

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 can have several conditions joined with AND) keeps each pair just once and never pairs a game with itself. Without it, you'd get Castle Siege with Castle Siege, and both Castle Siege with Tiny Kingdoms and Tiny Kingdoms with Castle Siege.