Self-joins & aliases

Join a table to itself

Lesson 2 of 20 · 9 min · Module 1: Joins beyond the basics

Sometimes rows point to other rows in the same table: a customer referred by another customer, an employee and their manager. A self-join reads the table twice under two different names.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Read one table under two aliases
Join a row to a related row in the same table
Replace NULL with a readable value

The idea

Give each copy its own alias, for example c for the customer and r for the referrer, and join them on r.id = c.referred_by. Use a LEFT JOIN so customers without a referrer stay in the result.

SELECT e.name, m.name AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id;

01Two names, one table

customers AS c and customers AS r are the same data, but SQL treats them as two separate sources. Every column must be qualified, such as c.name or r.name, or the query becomes ambiguous.

02Follow the pointer

c.referred_by stores the id of another customer. Matching it to r.id finds that customer’s row, exactly like a foreign key to a different table.

03COALESCE picks the first non-NULL value

COALESCE(r.name, 'nobody') returns the referrer’s name when it exists and the fallback otherwise. It works with any number of arguments.

SELECT COALESCE(NULL, NULL, 'third');

Output

third

Your turn

Return each customer’s name and the name of the customer who referred them as referred_by, or 'nobody' when there is no referrer. Sort by name.

What the checks look for

Shows every customer with their referrer or “nobody”

Try it in the editor →

Take this with you

A self-join is an ordinary join where both sides happen to be the same table.

Go a little further

Count how many customers each person referred, including people who referred nobody.