CROSS & SELF JOIN

Not every join relates two different sets of rows through a foreign key. A CROSS JOIN combines every row with every other row regardless of any relationship, and a self join joins a table to itself to relate rows within the same table.

CROSS JOIN: the Cartesian product

A CROSS JOIN pairs every row in one table with every row in the other, with no matching condition at all. A table with 3 rows crossed with a table of 4 rows produces 12 result rows.

Every combination of size and color for a product variant

SQL

SELECT s.size_label, c.color_name
FROM sizes s
CROSS JOIN colors c
ORDER BY s.size_label, c.color_name;

size_label | color_name
-----------+-----------
L          | Black
L          | White
M          | Black
M          | White
S          | Black
S          | White

This is exactly the kind of query you would run to generate every possible product variant (small/medium/large × black/white) before inserting them as rows into a product_variants table.

A missing ON clause can accidentally cross-join

Older comma-style join syntax — FROM a, b WHERE ... — or simply forgetting an ON clause on a JOIN produces a Cartesian product without you intending one. On two large tables this can silently multiply your row count into the millions and make a query appear to hang. If a join result looks unexpectedly huge, check first whether every JOIN actually has a correct ON condition.

SELF JOIN: joining a table to itself

A self join is just a regular join where both sides happen to be the same table. Because the table name would otherwise be ambiguous, you must give each side a different alias.

The classic example is an employees table where each row has a manager_id that also refers to a row in employees:

Each employee alongside their manager's name

SQL

SELECT e.employee_id, e.first_name AS employee_name,
       m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;

employee_id | employee_name | manager_name
------------+---------------+--------------
          1 | Alicia        | NULL
          2 | Ben           | Alicia
          3 | Chidi         | Alicia

Here e and m refer to the exact same underlying employees table, but each alias represents a different logical role in the query: e is “the employee,” m is “their manager.” A LEFT JOIN is used here so that Alicia — who has no manager — still appears in the result, with manager_name as NULL.

CROSS JOIN produces every combination of rows from two tables
A missing or forgotten ON clause can turn a regular join into an accidental cross join
A self join relates rows in a table to other rows in the same table
Self joins require aliasing the same table differently on each side