SQLite CROSS JOIN

A CROSS JOIN combines every row from the first table with every row from the second table. It produces a result where the total row count equals the number of rows in table A multiplied by the number of rows in table B. This is called a Cartesian product.

The Cartesian Product Explained

Table A: colors        Table B: sizes
───────────────        ──────────────
color                  size
──────                 ──────
Red                    Small
Blue                   Medium
Green                  Large

CROSS JOIN result (3 × 3 = 9 rows):
────────────────────────────────────────
color │ size
──────┼────────
Red   │ Small
Red   │ Medium
Red   │ Large
Blue  │ Small
Blue  │ Medium
Blue  │ Large
Green │ Small
Green │ Medium
Green │ Large

CROSS JOIN Syntax

SELECT columns
FROM table_a
CROSS JOIN table_b;

-- Equivalent comma syntax (produces identical result):
SELECT columns
FROM table_a, table_b;

Practical Example: Product Variants

CREATE TABLE colors (color TEXT);
INSERT INTO colors VALUES ('Red'),('Blue'),('Green');

CREATE TABLE sizes (size TEXT);
INSERT INTO sizes VALUES ('Small'),('Medium'),('Large');

SELECT color, size,
       color || '-' || size AS variant_name
FROM colors
CROSS JOIN sizes
ORDER BY color, size;

color │ size   │ variant_name
──────┼────────┼─────────────
Blue  │ Large  │ Blue-Large
Blue  │ Medium │ Blue-Medium
Blue  │ Small  │ Blue-Small
Green │ Large  │ Green-Large
Green │ Medium │ Green-Medium
Green │ Small  │ Green-Small
Red   │ Large  │ Red-Large
Red   │ Medium │ Red-Medium
Red   │ Small  │ Red-Small

Real-World Use Cases

Use Case 1: Generate a Full Schedule Matrix

-- All employee + shift combinations
CREATE TABLE employees (name TEXT);
INSERT INTO employees VALUES ('Alice'),('Bob'),('Carol');

CREATE TABLE shifts (shift TEXT);
INSERT INTO shifts VALUES ('Morning'),('Evening'),('Night');

SELECT name, shift FROM employees CROSS JOIN shifts;

name  │ shift
──────┼────────
Alice │ Morning
Alice │ Evening
Alice │ Night
Bob   │ Morning
Bob   │ Evening
Bob   │ Night
Carol │ Morning
Carol │ Evening
Carol │ Night

Use Case 2: Price Grid

-- Every product × every region for a pricing table
SELECT p.name AS product, r.region, p.base_price
FROM products p
CROSS JOIN regions r
ORDER BY p.name, r.region;

Use Case 3: Number Series Generator

-- Generate numbers 1 through 9 using CROSS JOIN
WITH digits(n) AS (
  VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)
)
SELECT a.n * 10 + b.n AS number
FROM digits a
CROSS JOIN digits b
WHERE a.n * 10 + b.n BETWEEN 10 AND 99
ORDER BY number;

Row Count Warning

CROSS JOIN grows fast:
──────────────────────────────────────────────────
Table A rows × Table B rows = Result rows
100    ×  100              = 10,000
1,000  ×  1,000            = 1,000,000
10,000 ×  10,000           = 100,000,000

A CROSS JOIN of two large tables can crash your query.
Always know the size of both tables before cross joining.

CROSS JOIN with WHERE Filter

Adding a WHERE clause to a CROSS JOIN filters the Cartesian product down to useful combinations.

-- All employee-shift pairs, but exclude Night shifts for Alice
SELECT name, shift
FROM employees
CROSS JOIN shifts
WHERE NOT (name = 'Alice' AND shift = 'Night');

name  │ shift
──────┼────────
Alice │ Morning
Alice │ Evening
Bob   │ Morning
Bob   │ Evening
Bob   │ Night
Carol │ Morning
Carol │ Evening
Carol │ Night

CROSS JOIN vs INNER JOIN

CROSS JOIN                    INNER JOIN
─────────────────────────     ─────────────────────────────
No ON condition needed        Requires ON condition
Every row pairs with every    Only matching rows pair
Result = A rows × B rows      Result ≤ min(A rows, B rows)
Use for: all combinations     Use for: related data lookups

Key Takeaways

CROSS JOIN pairs every row from table A with every row from table B
Result row count = rows in A × rows in B (can be very large)
No ON condition is needed or used
Common uses: generating combinations, schedules, pricing grids
Always consider result size before cross joining large tables

Previous lesson

Back to course

Next lesson