Cross Join | PostgreSQL Tutorial

Introduction to PostgreSQL CROSS JOIN

A CROSS JOIN combines each row from the first table with every row from the second table, producing the Cartesian product of the two tables. Unlike INNER JOIN or LEFT JOIN, CROSS JOIN has no join condition.

Syntax:

SELECT select_list
FROM table1
CROSS JOIN table2;

Equivalent comma syntax:

SELECT select_list
FROM table1, table2;

Equivalent using INNER JOIN ON true:

SELECT select_list
FROM table1
INNER JOIN table2 ON true;

If table1 has n rows and table2 has m rows, the result contains n × m rows.

Basic CROSS JOIN example

Create two small tables and cross-join them:

CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);
CREATE TABLE T2 (score INT PRIMARY KEY);

INSERT INTO T1 VALUES ('A'), ('B');
INSERT INTO T2 VALUES (1), (2), (3);

SELECT * FROM T1 CROSS JOIN T2;

Output (2 × 3 = 6 rows):

 label | score
-------+-------
 A     |     1
 B     |     1
 A     |     2
 B     |     2
 A     |     3
 B     |     3
(6 rows)

Practical examples

Scheduling — all employee-shift combinations:

-- All possible employee-shift pairings for staffing analysis
SELECT e.employee_name, s.shift_name
FROM employees e
CROSS JOIN shifts s
ORDER BY e.employee_name, s.shift_name;

Inventory — every product in every warehouse:

-- All product-warehouse combinations to identify stocking gaps
SELECT p.product_name, w.warehouse_name
FROM products p
CROSS JOIN warehouses w
ORDER BY w.warehouse_name, p.product_name;

CROSS JOIN tips

Calculate the expected row count (n × m) before running to avoid accidentally generating millions of rows.
Use the explicit CROSS JOIN keyword rather than the comma syntax — it makes the intent clear to anyone reading the query.
Add a WHERE clause after the join to filter the Cartesian product down to only the combinations you need.
During development, use LIMIT to inspect the output shape before retrieving the full result set.
Be especially careful when either table is large — a 1,000-row table crossed with another 1,000-row table produces 1,000,000 rows.

Frequently Asked Questions

What is a Cartesian product in a PostgreSQL CROSS JOIN?

A Cartesian product pairs each row from the first table with every row from the second table. If table T1 has 2 rows and table T2 has 3 rows, the CROSS JOIN result has 2 × 3 = 6 rows. There is no filtering condition — every possible combination is included.

Does CROSS JOIN require an ON clause?

No. CROSS JOIN has no join condition — adding ON or USING is a syntax error. If you want to filter the combined rows, use a WHERE clause after the join. Using INNER JOIN with ON true produces the same result as CROSS JOIN.

What are practical uses for CROSS JOIN?

Common use cases include: generating all combinations of attributes (e.g., all employee-shift pairs for scheduling), building test datasets that need every combination of values, creating date-series scaffolding by crossing a calendar table with a dimension table, and exploring all product-warehouse combinations in inventory management.

What is the comma syntax equivalent to CROSS JOIN?

Listing two tables separated by a comma in the FROM clause without a join condition is equivalent to CROSS JOIN: SELECT * FROM T1, T2 produces the same result as SELECT * FROM T1 CROSS JOIN T2. The explicit CROSS JOIN syntax is preferred for clarity.

How do I avoid accidentally producing a very large result set with CROSS JOIN?

Calculate the expected row count before running: n × m rows. For large tables this can be millions of rows. Add a WHERE clause to filter the Cartesian product down to the combinations you need, or use LIMIT during development to verify the output shape before retrieving the full result.