Module 3: Working with Multiple Tables•30 min

Self-Joins

Progress Tracking

When a Table Needs to Talk to Itself

A self-join is exactly what it sounds like: joining a table to itself. Sounds weird until you see the use cases. Then it makes perfect sense.

The classic example is the employee-manager relationship. Managers are employees too. They all live in the same table. To answer “Who manages whom?”, you need to join employees to employees.

Here’s the pattern — notice how we treat the same table as two different entities using aliases:

SQL

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

We’re treating the employees table as two different tables: ‘e’ for employees, ‘m’ for managers. The join connects each employee to their manager. LEFT JOIN because the CEO has no manager.

Aliases are required

Self-joins absolutely require aliases. Without them, SQL can’t tell which “copy” of the table you’re referring to. You’ll get syntax errors.

Comparing Rows Within a Table

Self-joins shine when you need to compare rows to other rows in the same table: “Find employees who earn more than someone else in their department”, “Show me products priced higher than others in their category”.

Table: employees

id	first_name	last_name	salary	joining_date	department
1001	Monika	Arora	100000	2023-01-12	HR
1002	Niharika	Verma	80000	2023-01-05	Admin
1003	Vishal	Singhal	300000	2023-01-02	HR
1004	Amitah	Singh	500000	2022-12-15	Admin
1005	Vivek	Bhati	500000	2022-11-16	Admin

Tables: employees

Inequality joins are expensive

Self-joins with inequality conditions (>, <, <>) are expensive. Without a scope constraint, you’re comparing n×n pairs. On 10,000 employees, that’s 100 million comparisons. Always add a scope like “same department” to keep it manageable.

SQL

-- DANGEROUS: Compares every row to every other row
SELECT *
FROM employees a
JOIN employees b
  ON a.salary > b.salary;
-- 10,000 employees = 100 million comparisons!

-- SAFER: Scope to same department
SELECT *
FROM employees a
JOIN employees b
  ON a.department = b.department
  AND a.salary > b.salary;
-- Much smaller comparison sets

Finding Related Pairs

Another common pattern: finding pairs of related items. Notice how a.id < b.id prevents both duplicate pairs and self-pairs:

Table: employee

id	first_name	last_name	age	sex	employee_title	department	salary	target	bonus	email	city	address	manager_id
5	Max	George	26	M	Sales	Sales	1300	200	150	Max@company.com	California	2638 Richards Avenue	1
13	Katty	Bond	56	F	Manager	Management	150000	0	300	Katty@company.com	Arizona		1
11	Richerd	Gear	57	M	Manager	Management	250000	0	300	Richerd@company.com	Alabama		1
10	Jennifer	Dion	34	F	Sales	Sales	1000	200	150	Jennifer@company.com	Alabama		13
19	George	Joe	50	M	Manager	Management	100000	0	300	George@company.com	Florida	1003 Wyatt Street	1

Tables: employee

The a.id < b.id condition is important. Without it, you get self-pairs (Alice paired with herself) and duplicate pairs (Alice-Bob and Bob-Alice).

Use < not !=

Use a.id < b.id (not !=) to get each pair exactly once. The inequality ensures you only get pairs where the first ID is smaller.

Table: transportation_numbers

index	number
1	5
2	3
3	7
4	1
5	0

Tables: transportation_numbers

A Note on Performance

Self-joins can get expensive. Here’s how to keep them under control:

Always add a scope predicate (same department, same category, same year)
For ranking/comparison tasks, consider window functions instead (Module 6)
Test on small data first to understand the row multiplication

Window functions often replace self-joins with cleaner, faster code. But self-joins are still essential for certain patterns, especially hierarchical data.

Employees With the Same Salary

Use a self-join to find employees who earn the same salary as another employee.

Table: worker

worker_id	first_name	last_name	salary	joining_date	department
1	Monika	Arora	100000	2014-02-20	HR
2	Niharika	Verma	80000	2014-06-11	Admin
3	Vishal	Singhal	300000	2014-02-20	HR
4	Amitah	Singh	500000	2014-02-20	Admin
5	Vivek	Bhati	500000	2014-06-11	Admin

Tables: worker

Matching Users Pairs

Find matching employee pairs using multiple join conditions on the same table.

Table: facebook_employees

id	location	age	gender	is_senior
0	USA	24	M	FALSE
1	USA	31	F	TRUE
2	USA	29	F	FALSE
3	USA	33	M	FALSE
4	USA	36	F	TRUE

Tables: facebook_employees

Key Takeaways

Self-joins join a table to itself using different aliases
Essential for: hierarchies (org charts), row comparisons, finding pairs
Use a.id < b.id to avoid duplicate and self pairs
Consider window functions as an alternative for ranking tasks

What’s Next

You’ve now mastered all the join types. Time to put them together: joining three, four, or more tables in a single query. That’s where things get interesting.

Next upContinue →

Joining Multiple Tables

35 min