Beyond the basic two-table join, three patterns recur: the self join (a table joined to itself, e.g. employee to manager), the cross join (every combination of two tables), and multi-table joins that chain three or more tables together.
Self join & cross join
A self join treats one table as two by giving it two aliases — perfect for hierarchies like employee/manager. A cross join pairs every row of one table with every row of another (a Cartesian product), used intentionally for combinations or accidentally when you forget a join condition.
Self join: employee with manager name
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; -- e and m are the SAME table, aliased twice
Multi-table join: employee, department, project
SELECT e.name, d.dept_name, p.proj_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id JOIN projects p ON d.dept_id = p.dept_id;
⚡ The edge
- A self join aliases the table twice (e for the employee, m for the manager) so the database can treat the two copies independently. Use a LEFT join so the top boss (no manager) still appears.
- A cross join returns rows(A) × rows(B). It's occasionally useful (e.g. generate all size/colour combinations) but is usually a bug — a missing ON condition.
Worked example
Show each employee next to their manager's name.
- The manager is another row in the same employees table, linked by manager_id -> emp_id.
- Join employees to itself with two aliases: e (employee) and m (manager).
- Use a LEFT join so an employee with no manager still appears with a NULL manager.
Answer: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id=m.emp_id;
Worked example
How many rows does a CROSS JOIN of 6 employees and 3 departments produce?
- A cross join pairs every left row with every right row.
- That is 6 × 3 combinations.
- So it produces 18 rows.
Answer: 18 rows (6 × 3) — the Cartesian product.
⚠ Watch out
- A self join must alias the table twice, or the column references are ambiguous.
- A missing join condition yields an accidental cross join — often a runaway result.
- Chaining joins multiplies rows if a join key isn't unique — watch for unexpected duplicates.