The earlier Joins lesson covered INNER, LEFT, RIGHT, and FULL — every one of them combining rows from two different tables. These last two joins are a bit different.
Not a distinct join type in SQL syntax — any of the earlier joins (usually INNER or LEFT) used on a table paired with itself, under two different aliases. The classic use case: a table where one row references another row in the same table, like an employee referencing their manager.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT
);
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;e and m here).Common Mistake
Forgetting to alias the two copies of the table makes it impossible for the query to know which "id" or "name" is being referred to — this is the one join type where an alias isn't optional.
Combines every row in one table with every row in another — no matching condition at all. A table of 3 rows CROSS JOINed with a table of 4 rows produces 12 rows.
SELECT sizes.size, colors.color
FROM sizes
CROSS JOIN colors;
-- If sizes has S/M/L and colors has Red/Blue,
-- this produces all 6 combinations: S-Red, S-Blue, M-Red, M-Blue, L-Red, L-Blue| Join type | Rows produced |
|---|---|
| SELF JOIN | A regular join (INNER/LEFT/etc.), just on the same table twice |
| CROSS JOIN | Every row × every row — no matching condition |