Self joins and cross joins

Why join a table to itself?

In COMPANY, manager_id points to another row of the same employees table. Rahul's manager_id is 1, which is Meera's id. To show "Rahul Sharma — Meera Iyer" you need two copies of employees: one playing the employee, one playing the manager. SQL does not copy anything; you simply name the table twice with two different aliases.

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;
-- Meera Iyer   | NULL         <- the boss has no manager
-- Rahul Sharma | Meera Iyer
-- Anita Desai  | Rahul Sharma
-- ... 11 rows

The aliases are required here. Without them, employees.name would be ambiguous: which copy do you mean? Choose aliases that say the role, like e and m, or emp and mgr.

Comparing a row with its partner

Once both copies are side by side you can compare them. A classic interview question is "find employees who earn more than their manager".

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;
-- (no rows)

An empty result is not always a bug. Check the data before "fixing" a query. Here a quick look at the pairs shows the smallest gap is 25000, for Anita and Vikram under Rahul. A more useful question for this data is the size of that gap:

SELECT e.name AS employee, m.name AS manager, m.salary - e.salary AS gap
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
ORDER BY gap;
-- Anita Desai  | Rahul Sharma | 25000
-- Vikram Singh | Rahul Sharma | 25000
-- Neha Joshi   | Farhan Ali   | 37000
-- ...

Pairs within a group

Another self join finds pairs, for example two employees in the same department who could cover for each other. Join on the same dept_id, and add a.id < b.id. Without it, you would get each person paired with themselves (Meera–Meera) and every pair twice (Meera–Rahul and Rahul–Meera).

SELECT a.name, b.name, a.dept_id
FROM employees AS a
JOIN employees AS b ON a.dept_id = b.dept_id AND a.id < b.id;
-- Meera Iyer | Rahul Sharma | 1
-- ...
-- Farhan Ali | Neha Joshi   | 3
-- 10 pairs

CROSS JOIN: every combination

A cross join has no ON. It pairs every row of the first table with every row of the second, so the result has rows(a) × rows(b) rows. This is also called the Cartesian product. Usually it is an accident (a forgotten ON), but sometimes all combinations are exactly what you want.

SELECT s.size, c.colour
FROM sizes AS s
CROSS JOIN colours AS c;
-- S | White
-- S | Indigo
-- M | White
-- ... 3 sizes × 2 colours = 6 variants

Another common use is a calendar grid: every date × every store, then LEFT JOIN the sales onto it. Days with no sales then show as 0 instead of being missing, which matters for charts. Module 6 shows how to generate the dates.

Common mistakes

• Using an inner self join for the employee–manager list and losing the top boss. Use LEFT JOIN.
• Getting each pair twice and people paired with themselves. Add a.id < b.id.
• Mixing up the roles: ON m.id = e.manager_id means "m is e's manager". Writing ON e.id = m.manager_id swaps them.
• An accidental cross join from a missing ON, giving a huge result. Check the row count.