Self join

Part of the Fundamentals section of Coddy's SQL journey — lesson 46 of 72.

Self-joins are different types of joins. As of now, we talked about multiple table joins, but self-joins are joined to the same table. A classic example would be a table of employees.

employee_id	employee_name	manager_id
1	Minke	2
2	Temur	3
3	Tatjana	4
4	Marinela

Every employee has a manager except the highest manager, and every manager is also an employee.

The problem: For each employee we want to know the manager's name.

SELECT e2.employee_id, e2.employee_name, e1.employee_name as manager_name
FROM employees as e1
JOIN employees as e2 ON e1.employee_id = e2.manager_id

We join between the same table except one time we call it e1 and the second time it is e2. The join is between the fields employee_id and manager_id.

The result:

employee_id	employee_name	manager_name
1	Minke	Temur
2	Temur	Tatjana
3	Tatjana	Marinela

Challenge

Medium

Available tables and columns:
friends: id, name, friend_id

Find pairs of friends who are mutual friends (mutual friendship exists when person A's friend_id points to person B AND person B's friend_id points to person A). Display both friends names in a single row. Name the columns friend1 and friend2.

Note: Include in the WHERE clause the criteria friend1.id < friend2.id so that it will not include duplicate pairs

Cheat sheet

Self-joins allow you to join a table to itself using table aliases. This is useful when you need to compare rows within the same table or establish relationships between records in the same table.

Basic self-join syntax:

SELECT columns
FROM table_name AS alias1
JOIN table_name AS alias2 ON alias1.column = alias2.column

Example with employees and managers:

SELECT e2.employee_id, e2.employee_name, e1.employee_name as manager_name
FROM employees as e1
JOIN employees as e2 ON e1.employee_id = e2.manager_id

The key points:

Use different aliases (e.g., e1, e2) for the same table
Join condition connects related fields between the aliases
Useful for hierarchical data like employee-manager relationships

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Self join

Cheat sheet

All lessons in Fundamentals