Skip to main content

CROSS JOIN

Learning Focus

Use this lesson to understand CROSS JOIN with practical syntax and examples.

Concept Overview​

CROSS JOIN returns the Cartesian product of two inputs: every row from the left side is paired with every row from the right side.

If table A has m rows and table B has n rows, the result has m * n rows.

Why is it important?​

  • Generating combinations: sizes x colors, users x feature flags, days x products
  • Building grids: create a complete matrix of dimensions for reporting
  • Understanding join mechanics: accidental Cartesian products are a common bug when you forget a join condition

Where does it fit?​

CROSS JOIN is a join type alongside INNER/LEFT/RIGHT. Unlike other joins, it does not use an ON condition. In PostgreSQL, you also often see cross joins with VALUES or generate_series() for controlled dimension sets.

Syntax & Rules​

Core Syntax​

SELECT select_list
FROM a
CROSS JOIN b;

Equivalent syntax (comma join):

SELECT select_list
FROM a, b;

Available Options / Parameters​

PartWhat it doesNotes
CROSS JOINproduces all combinationsno ON clause
WHEREfilters after combinations are createduseful, but can still be expensive
VALUESinline small tablesgreat for controlled sets
generate_series()generate sequences (dates/numbers)common for calendars/time grids

Key Rules and Considerations​

  • Cross joins can explode in size quickly; always estimate row counts.
  • If you see unexpectedly huge results, check for a missing join condition in a regular join.
  • Prefer building combinations from small dimension sets, then join to facts.
  • Use LIMIT while experimenting.

Step-by-Step Examples​

Example 1: Controlled Combinations with VALUES (Beginner)​

SELECT s.size, c.color
FROM (VALUES ('S'), ('M'), ('L')) AS s(size)
CROSS JOIN (VALUES ('red'), ('blue')) AS c(color)
ORDER BY s.size, c.color;

Expected output:

 size | color
------+------
 L    | blue
 L    | red
 M    | blue
 M    | red
 S    | blue
 S    | red
(6 rows)

Explanation:

  • 3 sizes x 2 colors = 6 combinations.

Example 2: Product Variants (Intermediate)​

CREATE TABLE products (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);

INSERT INTO products (name) VALUES
  ('Laptop'),
  ('Monitor');

CREATE TABLE colors (
  color text PRIMARY KEY
);

INSERT INTO colors (color) VALUES
  ('Black'),
  ('Silver');

SELECT p.name AS product, c.color
FROM products p
CROSS JOIN colors c
ORDER BY p.product_id, c.color;

Expected output:

 product | color
---------+--------
 Laptop  | Black
 Laptop  | Silver
 Monitor | Black
 Monitor | Silver
(4 rows)

Explanation:

  • This is useful for generating variants before inserting into a product_variants table.

Example 3: Build a Calendar Grid with generate_series() (Advanced)​

-- Create a 3-day calendar and cross it with products
SELECT d.day::date, p.name
FROM generate_series('2026-03-01'::date, '2026-03-03'::date, interval '1 day') AS d(day)
CROSS JOIN products p
ORDER BY d.day, p.product_id;

Expected output:

 day        | name
------------+---------
 2026-03-01 | Laptop
 2026-03-01 | Monitor
 2026-03-02 | Laptop
 2026-03-02 | Monitor
 2026-03-03 | Laptop
 2026-03-03 | Monitor
(6 rows)

Explanation:

  • This pattern is common in reporting (show all days even when sales are 0).

Example 4: Accidental Cartesian Product (Missing Join Condition) (Advanced)​

In PostgreSQL, writing JOIN without an ON/USING clause is a syntax error, but you can still accidentally create a Cartesian product by using a comma join (which behaves like a cross join).

CREATE TABLE suppliers (
  supplier_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  supplier_name text NOT NULL
);

CREATE TABLE products_with_supplier (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  supplier_id bigint NOT NULL REFERENCES suppliers(supplier_id)
);

INSERT INTO suppliers (supplier_name) VALUES
  ('Acme'),
  ('Globex');

INSERT INTO products_with_supplier (name, supplier_id) VALUES
  ('Laptop', 1),
  ('Monitor', 2);

-- Wrong: comma join creates a Cartesian product
SELECT p.name, s.supplier_name
FROM products_with_supplier p, suppliers s
ORDER BY p.product_id, s.supplier_id;

Expected output (wrong):

 name    | supplier_name
---------+--------------
 Laptop  | Acme
 Laptop  | Globex
 Monitor | Acme
 Monitor | Globex
(4 rows)

Fix (use a real join condition):

SELECT p.name, s.supplier_name
FROM products_with_supplier p
JOIN suppliers s ON s.supplier_id = p.supplier_id
ORDER BY p.product_id;

Expected output (fixed):

 name    | supplier_name
---------+--------------
 Laptop  | Acme
 Monitor | Globex
(2 rows)

Explanation:

  • A Cartesian product is correct only when you truly want all combinations.
  • When you intended to match relationships, always use explicit JOIN ... ON ....

Practical Use Cases​

1) Generate all possible variants​

SELECT p.product_id, s.size, c.color
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c;

2) Scheduling grids (employees x shifts)​

SELECT e.employee_id, sh.shift_id
FROM employees e
CROSS JOIN shifts sh;

3) Complete time series reporting (days x store)​

SELECT day, store_id
FROM generate_series(current_date - 6, current_date, interval '1 day') AS g(day)
CROSS JOIN stores;

4) Test-case generation​

SELECT os.name AS os, br.name AS browser
FROM operating_systems os
CROSS JOIN browsers br;

5) Feature flag exposure matrix​

SELECT u.user_id, f.flag_key
FROM users u
CROSS JOIN feature_flags f;

Common Mistakes & Troubleshooting​

1) Using CROSS JOIN on large tables​

Wrong approach:

SELECT *
FROM big_fact_table
CROSS JOIN another_big_table;

Bad outcome:

  • Query can run for a long time or exhaust resources.

Fix:

  • Cross join only small dimension sets, or add constraints before cross joining.

2) Expecting an ON condition​

Wrong SQL:

FROM a CROSS JOIN b ON a.id = b.id

Typical error:

ERROR:  syntax error at or near "ON"

Fix:

FROM a JOIN b ON a.id = b.id

3) Confusing CROSS JOIN with INNER JOIN​

Wrong mental model:

  • expecting CROSS JOIN to match by keys.

Fix:

  • CROSS JOIN always returns all combinations; use an inner join for matched relationships.

4) Filtering after a cross join (too late)​

Wrong approach:

SELECT *
FROM a
CROSS JOIN b
WHERE b.type = 'small';

Bad outcome:

  • Still creates all combinations before filtering.

Fix:

SELECT *
FROM a
CROSS JOIN (SELECT * FROM b WHERE type = 'small') b;

Debugging checklist:

  1. Estimate row counts on both sides before running the query.
  2. Verify you actually need all combinations.
  3. If results are unexpectedly huge, look for a missing join condition.
  4. Use a small dimension set (VALUES) to validate logic.
  5. Use LIMIT and EXPLAIN when exploring.

Best Practices​

  • ✅ Use VALUES or small lookup tables as cross-join inputs. ❌ Avoid cross joining two large fact tables.
  • ✅ Filter dimension inputs before the cross join. ❌ Avoid relying on WHERE to “save you” after a huge Cartesian product.
  • ✅ Use cross joins to build complete grids for reporting. ❌ Avoid cross joins for matched relationships (use inner/outer joins instead).
  • ✅ Add LIMIT while developing queries. ❌ Avoid running a new cross join query in production without checking size.
  • ✅ Treat accidental Cartesian products as a bug. ❌ Avoid unqualified joins (or joins without ON) unless you truly want CROSS JOIN behavior.

Hands-On Practice​

Use this setup for the exercises:

CREATE TABLE practice_fruits (name text PRIMARY KEY);
CREATE TABLE practice_sizes (size_label text PRIMARY KEY);

INSERT INTO practice_fruits (name) VALUES ('Apple'), ('Banana');
INSERT INTO practice_sizes (size_label) VALUES ('Small'), ('Large');

Exercise 1 (Easy): All combinations​

Task: Return all fruit/size combinations.

-- Your SQL here

Solution:

SELECT f.name, s.size_label
FROM practice_fruits f
CROSS JOIN practice_sizes s
ORDER BY f.name, s.size_label;

Exercise 2 (Medium): Filtered combinations​

Task: Return only combinations where size is Large.

-- Your SQL here

Solution:

SELECT f.name, s.size_label
FROM practice_fruits f
CROSS JOIN practice_sizes s
WHERE s.size_label = 'Large'
ORDER BY f.name;

Exercise 3 (Advanced): Count combinations​

Task: Count how many rows the cross join produces.

-- Your SQL here

Solution:

SELECT COUNT(*) AS total_combinations
FROM practice_fruits
CROSS JOIN practice_sizes;

Connection to Other Concepts​

ConceptWhy it matters
INNER JOINinner joins need ON conditions; cross joins do not
WHEREfilters after the cross join result set exists
GROUP BYoften used to aggregate grids created by cross joins
generate_series()common PostgreSQL tool paired with cross joins for calendars
LEFT JOINused after cross joins to attach optional fact data

Visual Learning Diagram​

Common Pitfalls​

PitfallConsequencePrevention
Cross joining large tablesmassive result setscross join only small inputs; filter early
Forgetting an ON clause in a normal joinaccidental Cartesian productalways write explicit JOIN ... ON ...
Using CROSS JOIN for matched relationshipsincorrect resultsuse INNER JOIN/LEFT JOIN with keys
Filtering too latewasted workreduce inputs before cross joining
No LIMIT during explorationrunaway queriesstart with LIMIT and inspect

Quick Reference​

SELECT * FROM a CROSS JOIN b;
SELECT s.size, c.color FROM (VALUES ('S'),('M')) s(size) CROSS JOIN (VALUES ('red'),('blue')) c(color);
SELECT d::date FROM generate_series('2026-03-01'::date,'2026-03-03'::date,'1 day') AS g(d);
SELECT day, store_id FROM generate_series(current_date-6,current_date,'1 day') g(day) CROSS JOIN stores;
SELECT COUNT(*) FROM a CROSS JOIN b;

What's Next​