SQL SELF JOIN: Joining a Table to Itself

Lesson 5.7 · Reading time: ~5 min · Practice tasks ↓

A SQL self join joins a table to itself: the same table appears twice in the query under two different aliases. In this lesson you'll learn why aliases are required, how to find pairs of rows that share a value without duplicates, and how a self join reads a manager–employee hierarchy.

Sometimes the rows you want to connect are in the same table: an employee and their manager, two actors with the same last name, a flight and the connecting flight. There is no special SELF JOIN keyword. It is an ordinary INNER JOIN or LEFT JOIN in which both sides are the same table.

Aliases Are Required

The database must know which copy of the table each column comes from, so each copy needs its own alias:

SELECT a1.column, a2.column
FROM some_table AS a1
INNER JOIN some_table AS a2 ON a1.some_column = a2.some_column;

Think of a1 and a2 as two separate tables with the same content.

Finding Pairs of Rows

Which actors share a last name?

SELECT a1.first_name AS first_actor,
       a2.first_name AS second_actor,
       a1.last_name
FROM actor AS a1
INNER JOIN actor AS a2 ON a2.last_name = a1.last_name
                      AND a2.actor_id > a1.actor_id
ORDER BY a1.last_name, first_actor, second_actor;

Result: 108 pairs of actors with the same last name, each pair once.

The condition a2.actor_id > a1.actor_id does two jobs:

• it excludes matching an actor with themselves, since every actor has the same last name as themselves;
• it keeps only one of the two mirrored pairs: with <> you would get both "CHRISTIAN–DEBBIE AKROYD" and "DEBBIE–CHRISTIAN AKROYD".

Comparing Rows

A self join can also compare a row with other rows of the same table. Films that are longer than "ACADEMY DINOSAUR" and have the same rating:

SELECT f2.title, f2.rating, f2.length
FROM film AS f1
INNER JOIN film AS f2 ON f2.rating = f1.rating
                     AND f2.length > f1.length
WHERE f1.title = 'ACADEMY DINOSAUR'
ORDER BY f2.length DESC
LIMIT 10;

Result: the 10 longest PG films that are longer than "ACADEMY DINOSAUR" (86 minutes).

Hierarchies: Employees and Managers

The classic self join is a hierarchy stored in one table: each employee row has a manager_id that points to another row of the same table. Sakila has no such table, so here is the pattern:

SELECT e.name AS employee,
       m.name AS manager
FROM employee AS e
LEFT JOIN employee AS m ON m.employee_id = e.manager_id;

• e is the copy that plays the role of employees, m the role of managers.
• LEFT JOIN keeps the top manager, whose manager_id is NULL; with INNER JOIN that row would disappear.

One self join goes one level up. To walk the whole hierarchy to any depth, you'll use recursive CTEs in Lesson 6.5.

Key Takeaways:

• A self join is a normal join where both sides are the same table.
• Each copy of the table needs its own alias.
• Use a1.id < a2.id to exclude self-matches and mirrored duplicate pairs.
• For hierarchies, join manager_id to the same table's key; use LEFT JOIN to keep the top level.

Frequently Asked Questions

What is a self join in SQL?

A join of a table with itself. The table is listed twice with different aliases, and the join condition connects rows of one copy with rows of the other, for example an employee with their manager.

How do I avoid duplicate pairs in a self join?

Compare the keys with < instead of <>: a1.id < a2.id. It excludes a row matching itself and keeps only one of the two mirrored pairs.

Is SELF JOIN a separate SQL keyword?

No. You write a self join with INNER JOIN or LEFT JOIN; the only difference is that both tables in the join are the same.