sqlstreak

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.id for 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.

Practise it now