CROSS JOIN

CROSS JOIN returns every combination of rows from two inputs.

It is useful for matrix-style generation (sizes x colors, dates x resources), but dangerous if used accidentally.

Core Concepts

flowchart TD
  A[n rows in table A] --> C[Cartesian product]
  B[m rows in table B] --> C
  C --> D[n x m rows returned]

Left rows	Right rows	Result rows
2	3	6
5	4	20

Code Examples

-- Setup tiny dimension tables.
CREATE TABLE sizes (
  size TEXT NOT NULL
);

CREATE TABLE colors (
  color TEXT NOT NULL
);

INSERT INTO sizes (size) VALUES ('S'), ('M');
INSERT INTO colors (color) VALUES ('Red'), ('Blue'), ('Green');

Expected output
Two sizes and three colors inserted.

-- Generate all size-color combinations.
SELECT s.size, c.color
FROM sizes AS s
CROSS JOIN colors AS c
ORDER BY s.size, c.color;

size	color
M	Blue
M	Green
M	Red
S	Blue
S	Green
S	Red

SQLite-Specific Nuances

SQLite Nuance

A comma-separated table list in FROM can also produce a Cartesian product.

Use explicit CROSS JOIN for clarity.

Common Pitfalls / Best Practices

Pitfall

Forgetting join conditions in multi-table queries and accidentally creating huge Cartesian outputs.

Best Practice

Use CROSS JOIN only when combinations are intentional, and estimate row count (left_count * right_count) first.

Quick Challenge

If table A has 4 rows and table B has 7 rows, how many rows does CROSS JOIN return?

View Solution

4 * 7 = 28 rows.