Self joins, RIGHT, FULL & CROSS

A table joined to itself, and the rest of the join family.

The rest of the join family

A self join joins a table to itself — the classic case is an org chart, where employees.manager_id points back into employees. Use two aliases so Postgres can tell the copies apart:

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

The rest complete the set: - RIGHT JOIN — keeps every row of the right table (a mirror of LEFT). - FULL OUTER JOIN — keeps unmatched rows from both sides. - CROSS JOIN — every combination of both tables (the Cartesian product).

In a self join, always give the two output columns distinct names.

A self join pairs each row with the row its manager_id points at. Alice Reed has no manager, so an inner join loses her entirely — a LEFT JOIN keeps her with a NULL manager.

Pick the join by which side you refuse to lose. CROSS JOIN is the odd one out: it has no ON clause at all, so every left row pairs with every right row.