A table joined to itself
Some tables contain a relationship to their own rows. In employees, manager_id holds the id of another employee. To show each person's manager by name, you need the employees table twice: once for the employee, once for their manager. A self join does that by giving the same table two aliases.
SELECT e.name AS employee, e.job_title, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;+-------------+---------------------+-------------+
| employee | job_title | manager |
+-------------+---------------------+-------------+
| Meera Iyer | Chief Executive | NULL |
| Tom Hughes | Head of Sales | Meera Iyer |
| Nina Petrov | Engineering Manager | Meera Iyer |
| Omar Haddad | Senior Developer | Nina Petrov |
| Paula Gomez | Developer | Nina Petrov |
| Quinn Baker | Sales Executive | Tom Hughes |
| Rina Das | Sales Executive | Tom Hughes |
| Sam Wright | Developer | Nina Petrov |
| Tara Singh | HR Manager | Meera Iyer |
| Uma Reddy | Junior Developer | Omar Haddad |
| Victor Lang | Consultant | Meera Iyer |
+-------------+---------------------+-------------+Think of e and m as two separate copies of the table. The join condition links the employee's manager_id to the manager's id. A LEFT JOIN keeps Meera, whose manager_id is NULL: her manager shows as NULL. An inner join would have dropped her.
Counting direct reports
Flip the roles: for each manager, count the people who report to them.
SELECT m.name AS manager, COUNT(e.id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC, m.name;+-------------+----------------+
| manager | direct_reports |
+-------------+----------------+
| Meera Iyer | 4 |
| Nina Petrov | 3 |
| Tom Hughes | 2 |
| Omar Haddad | 1 |
+-------------+----------------+Comparing a row with its related row
Because both aliases are full rows, you can compare them. Find employees who earn more than their manager:
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;No rows come back in this data, because every manager earns more than their reports. Try changing a salary with UPDATE and run it again to watch a row appear.
Pairs of rows in the same table
Self joins can also find pairs. Which customers live in the same country?
SELECT a.first_name AS first_customer, b.first_name AS second_customer, a.country
FROM customers a
JOIN customers b ON a.country = b.country AND a.id < b.id
ORDER BY a.country, a.id;+----------------+-----------------+---------+
| first_customer | second_customer | country |
+----------------+-----------------+---------+
| Asha | Farid | India |
| Asha | Kavya | India |
| Farid | Kavya | India |
+----------------+-----------------+---------+The a.id < b.id condition does two jobs. It stops a customer pairing with themselves (a.id = b.id is not less than), and it lists each pair only once. Without it you would see Asha with Farid and Farid with Asha as separate rows.
Walking a hierarchy
One self join shows one level. To see the manager's manager, join once more:
SELECT e.name AS employee, m.name AS manager, mm.name AS managers_manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
LEFT JOIN employees mm ON mm.id = m.manager_id
WHERE e.id IN (4, 10);+-------------+-------------+------------------+
| employee | manager | managers_manager |
+-------------+-------------+------------------+
| Omar Haddad | Nina Petrov | Meera Iyer |
| Uma Reddy | Omar Haddad | Nina Petrov |
+-------------+-------------+------------------+For a hierarchy of unknown depth you need a recursive common table expression, which a later lesson covers.
CROSS JOIN: every combination
A CROSS JOIN has no ON condition. It pairs every row of the first table with every row of the second, which is called a Cartesian product. The size multiplies:
SELECT (SELECT COUNT(*) FROM categories) AS categories,
(SELECT COUNT(*) FROM departments) AS departments,
(SELECT COUNT(*) FROM categories CROSS JOIN departments) AS combinations;+------------+-------------+--------------+
| categories | departments | combinations |
+------------+-------------+--------------+
| 5 | 4 | 20 |
+------------+-------------+--------------+That is 5 x 4 = 20. A cross join sounds like a mistake, but it is useful for building a grid, such as every product against every month for a sales report with zeros, or every size with every colour:
SELECT c.name AS category, d.name AS department
FROM categories c
CROSS JOIN departments d
WHERE c.id <= 2
ORDER BY c.id, d.id;+-------------+-------------+
| category | department |
+-------------+-------------+
| Electronics | Engineering |
| Electronics | Sales |
| Electronics | HR |
| Electronics | Finance |
| Books | Engineering |
| Books | Sales |
| Books | HR |
| Books | Finance |
+-------------+-------------+The accidental cross join
The dangerous version is the one you did not intend. If you list two tables and forget the condition, you get a Cartesian product:
SELECT COUNT(*) AS rows_returned FROM customers, orders;+---------------+
| rows_returned |
+---------------+
| 240 |
+---------------+Twelve customers times twenty orders gives 240 rows of mostly meaningless pairs. On two tables with a million rows each, that is a trillion rows and a very unhappy server. If a join returns far more rows than either table, check the ON condition first.
Common mistakes
- Using the same alias twice or omitting aliases in a self join.
- Using INNER JOIN for a hierarchy and losing the top of the tree.
- Forgetting
a.id < b.idand getting duplicates and self-pairs. - Cross joining large tables by accident.
- Joining on the wrong columns, such as
e.id = m.id, which just matches each row to itself.
Exercise
Write a query that lists each pair of employees who work in the same department, showing both names and the department id, each pair once. Then list every employee with the name of their manager's manager (NULL when there is none).
Show one possible answer
SELECT a.name AS employee_1, b.name AS employee_2, a.department_id
FROM employees a
JOIN employees b ON a.department_id = b.department_id AND a.id < b.id
ORDER BY a.department_id, a.id, b.id;
SELECT e.name, mm.name AS managers_manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
LEFT JOIN employees mm ON mm.id = m.manager_id
ORDER BY e.id;+-------------+-------------+---------------+
| employee_1 | employee_2 | department_id |
+-------------+-------------+---------------+
| Meera Iyer | Nina Petrov | 1 |
| Meera Iyer | Omar Haddad | 1 |
| Meera Iyer | Paula Gomez | 1 |
| Meera Iyer | Sam Wright | 1 |
| Meera Iyer | Uma Reddy | 1 |
| Nina Petrov | Omar Haddad | 1 |
| Nina Petrov | Paula Gomez | 1 |
| Nina Petrov | Sam Wright | 1 |
| Nina Petrov | Uma Reddy | 1 |
| Omar Haddad | Paula Gomez | 1 |
| Omar Haddad | Sam Wright | 1 |
| Omar Haddad | Uma Reddy | 1 |
| Paula Gomez | Sam Wright | 1 |
| Paula Gomez | Uma Reddy | 1 |
| Sam Wright | Uma Reddy | 1 |
| Tom Hughes | Quinn Baker | 2 |
| Tom Hughes | Rina Das | 2 |
| Quinn Baker | Rina Das | 2 |
+-------------+-------------+---------------+
+-------------+------------------+
| name | managers_manager |
+-------------+------------------+
| Meera Iyer | NULL |
| Tom Hughes | NULL |
| Nina Petrov | NULL |
| Omar Haddad | Meera Iyer |
| Paula Gomez | Meera Iyer |
| Quinn Baker | Meera Iyer |
| Rina Das | Meera Iyer |
| Sam Wright | Meera Iyer |
| Tara Singh | NULL |
| Uma Reddy | Nina Petrov |
| Victor Lang | NULL |
+-------------+------------------+Victor has no department, so he never pairs with anyone: a NULL department id does not equal anything, including another NULL.
Key points
- A self join treats one table as two by giving it two aliases, for example employees e and managers m.
- Use LEFT JOIN for hierarchies so the top person, who has no manager, still appears.
- To compare each pair of rows once, join on a.id < b.id.
- CROSS JOIN pairs every row of one table with every row of another. The result has rows_a x rows_b rows, so use it deliberately.