MySQL + SQL · Lesson 69

CROSS JOIN and SELF JOIN

CROSS JOIN

CROSS JOIN pairs every row of one table with every row of another (Cartesian product). No ON condition.

SELECT s.name, sub.subject
FROM students s
CROSS JOIN subjects sub;

If 3 students and 4 subjects, you get 12 rows.

SELF JOIN

A SELF JOIN joins a table to itself, using two aliases. Useful for hierarchy (e.g. employee and their manager in the same table).

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

Summary

CROSS JOIN = every combination (Cartesian product).
SELF JOIN = a table joined to itself using aliases.