Every join so far has combined two different tables, but SQL doesn't actually require that — you can join a table to itself. This is called a self join, and while the syntax looks unusual the first time you see it, it solves a very common problem: relating rows within the same table to each other, like employees to their managers, or cities to the cities they border.

Consider an employees table with a manager_id column, where each employee's manager is simply another row in that same table:

id | name    | manager_id
---|---------|------------
1  | Grace   | NULL
2  | Daniel  | 1
3  | Priya   | 1
4  | Omar    | 2

Grace (id 1) has no manager — she's at the top. Daniel and Priya both report to Grace, and Omar reports to Daniel. The manager_id column stores another row's id from this exact same table, which is what makes a self join necessary.

Why You Need Table Aliases

Since both sides of the join come from the same employees table, you can't just write employees.name twice — SQL wouldn't know which "copy" you mean. The fix is to give the table two different aliases, effectively treating it as two separate tables for the purposes of the query: one representing the employee, and one representing their manager.

SQL Self Join Example: Finding Each Employee's Manager

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

Result:

employee_name | manager_name
--------------|-------------
Grace | NULL
Daniel | Grace
Priya | Grace
Omar | Daniel

Here e is the alias for "the employee" and m is the alias for "their manager" — both pointing at the same underlying employees table. A LEFT JOIN is used (rather than an INNER JOIN) specifically so that Grace, who has no manager, still shows up in the results with manager_name as NULL instead of being dropped entirely.

Another Common Use Case: Comparing Rows Within the Same Table

Self joins also show up whenever you need to compare rows against each other — for example, finding every pair of employees in the same department, or every employee who earns more than a specific colleague:

SELECT a.name AS employee, b.name AS colleague
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id AND a.id <> b.id;

The a.id <> b.id condition is essential here — without it, every employee would be matched against themselves, which isn't useful. This pattern (same table, two aliases, a condition excluding matching an employee to themselves) is the standard shape of almost any self join you'll write.