PostgreSQL CROSS JOIN
Create a Cartesian product combining every row from both tables
✖️ What is CROSS JOIN?
CROSS JOIN produces a Cartesian product, combining every row from the first table with every row from the second table. No matching condition is needed.
-- Basic CROSS JOIN syntax
SELECT columns
FROM table1
CROSS JOIN table2;
Key CROSS JOIN Concepts
Cartesian Product
Every row paired with every row
-- 3 colors × 2 sizes = 6 combinations
SELECT * FROM colors
CROSS JOIN sizes;No ON Clause
No join condition required
-- Simple syntax without conditions
SELECT * FROM table1
CROSS JOIN table2;Result Size
Rows = Table1 rows × Table2 rows
-- If table1 has 10 rows and table2 has 5
-- Result will have 10 × 5 = 50 rows
SELECT COUNT(*) FROM table1 CROSS JOIN table2;Use Cases
Generate combinations and test data
-- Create all product-size combinations
SELECT p.name, s.size
FROM products p
CROSS JOIN sizes s;🔹 Basic CROSS JOIN Example
Let's create all possible combinations of colors and sizes. CROSS JOIN is perfect for generating product variants, test scenarios, or any situation requiring all possible pairings.
-- Sample tables
CREATE TABLE colors (
color_id SERIAL PRIMARY KEY,
color_name VARCHAR(50)
);
CREATE TABLE sizes (
size_id SERIAL PRIMARY KEY,
size_name VARCHAR(10)
);
-- Insert sample data
INSERT INTO colors (color_name) VALUES ('Red'), ('Blue'), ('Green');
INSERT INTO sizes (size_name) VALUES ('Small'), ('Large');
-- CROSS JOIN query
SELECT
c.color_name,
s.size_name
FROM colors c
CROSS JOIN sizes s;Output:
| color_name | size_name |
|---|---|
| Red | Small |
| Red | Large |
| Blue | Small |
| Blue | Large |
| Green | Small |
| Green | Large |
🔹 Alternative CROSS JOIN Syntax
You can write CROSS JOIN using comma-separated tables in the FROM clause. Both syntaxes produce identical results, but explicit CROSS JOIN is more readable and modern.
-- Method 1: Using CROSS JOIN keyword (Recommended)
SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;
-- Method 2: Using comma syntax (Old style)
SELECT c.color_name, s.size_name
FROM colors c, sizes s;Output (Both methods):
Both queries produce the same 6 rows (3 colors × 2 sizes)
🔹 Practical CROSS JOIN Example
Generate a schedule by combining employees with shifts. CROSS JOIN creates all possible employee-shift assignments, useful for rotation planning or availability matrices.
-- Create all employee-shift combinations
SELECT
e.employee_name,
s.shift_time,
s.shift_day
FROM employees e
CROSS JOIN shifts s
ORDER BY e.employee_name, s.shift_day;Output:
| employee_name | shift_time | shift_day |
|---|---|---|
| Alice | Morning | Monday |
| Alice | Evening | Monday |
| Bob | Morning | Monday |
| Bob | Evening | Monday |
🔹 CROSS JOIN with WHERE Filter
Add WHERE clause to filter the Cartesian product. This lets you generate combinations and then select only specific pairings based on business rules or constraints.
-- Generate combinations but filter results
SELECT
p.product_name,
c.category_name
FROM products p
CROSS JOIN categories c
WHERE p.price > 100
ORDER BY p.product_name;Output:
| product_name | category_name |
|---|---|
| Laptop | Electronics |
| Laptop | Computers |