SQL self joins and CROSS JOIN
Two joins that surprise people: joining a table to itself, and joining every row to every row. Both come up constantly in real work and in interviews.
Self join: employees and managers
When rows point at other rows in the same table, like manager_id pointing at another staff member, join the table to itself using two aliases.
SELECT s.name AS employee, s.role, m.name AS manager FROM staff s LEFT JOIN staff m ON m.id = s.manager_id;
Self join: pairs
To find pairs that share something, join on that column and use a.id < b.id so each pair appears once and nothing pairs with itself.
SELECT a.name AS first, b.name AS second, a.species FROM pets a JOIN pets b ON a.species = b.species AND a.id < b.id WHERE a.species = 'rabbit';
CROSS JOIN: every combination
CROSS JOIN pairs every row with every row, with no condition. Build a small list on the spot with SELECT … UNION ALL SELECT … to create grids.
SELECT m.name, s.size FROM menu_items m CROSS JOIN (SELECT 'S' AS size UNION ALL SELECT 'M' UNION ALL SELECT 'L') s WHERE m.category = 'drink';
Common mistakes
Forgetting distinct aliases in a self join, which gives ambiguous column errors.
Using
a.id <> b.idfor pairs, which lists every pair twice (A–B and B–A).An accidental cross join: a JOIN with a missing or wrong ON condition multiplies rows.
Questions
How do I get a whole hierarchy, not just direct managers?+
Use a recursive CTE (WITH RECURSIVE), which follows manager links any number of levels deep.
Is a comma between tables a CROSS JOIN?+
Yes: FROM a, b is an old-style cross join. Write CROSS JOIN explicitly so it's clearly intentional.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.