Docs
Docs/JOINs & Relationships/CROSS JOIN

CROSS JOIN

Generate all combinations of rows

CROSS JOIN produces a Cartesian product - every row from the first table combined with every row from the second table.

Syntax

-- Explicit CROSS JOIN
SELECT * FROM table1 CROSS JOIN table2;

-- Implicit (comma syntax)
SELECT * FROM table1, table2;

Example: Generate Combinations

-- Sizes and colors tables
-- sizes: S, M, L
-- colors: Red, Blue

SELECT sizes.size, colors.color
FROM sizes
CROSS JOIN colors;

-- Result (6 rows = 3 sizes x 2 colors):
-- S | Red
-- S | Blue
-- M | Red
-- M | Blue
-- L | Red
-- L | Blue

Use Case: Date Scaffolding

-- Create a row for every user for every date
-- (useful for reports with "missing" days)
SELECT 
    u.id AS user_id,
    u.name,
    d.date
FROM users u
CROSS JOIN (
    SELECT date('2024-01-01', '+' || n || ' days') AS date
    FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3)
) d;

Warning: CROSS JOIN can produce huge result sets! 1000 rows x 1000 rows = 1,000,000 rows. Use with caution.

Need help?

Join our Discord community for support and discussions.

Join Discord