What Is a CROSS JOIN?

SELECT *
FROM table_a
CROSS JOIN table_b;

Syntax

-- Explicit CROSS JOIN syntax
SELECT columns
FROM table_a
CROSS JOIN table_b;

-- Implicit syntax (comma-separated)
SELECT columns
FROM table_a, table_b;

How It Works

Basic Example

-- Pair every customer with every product
SELECT
    c.first_name,
    c.last_name,
    p.product_name
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name, p.product_name;

Generating Product Variants

-- Generate all size × color combinations for a product catalog
SELECT
    p.product_name,
    s.size_name,
    c.color_name,
    CONCAT(p.product_name, ' - ', s.size_name, ' - ', c.color_name) AS variant_name
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c
ORDER BY p.product_name, s.size_name, c.color_name;

Creating Test Data

-- Populate a user_permissions table with all user × permission pairs
INSERT INTO user_permissions (user_id, permission_id, granted)
SELECT
    u.user_id,
    p.permission_id,
    FALSE
FROM users u
CROSS JOIN permissions p
WHERE NOT EXISTS (
    SELECT 1
    FROM user_permissions up
    WHERE up.user_id = u.user_id
      AND up.permission_id = p.permission_id
);

CROSS JOIN with WHERE (Filtered Cross Join)

-- A CROSS JOIN with a WHERE clause can behave like an INNER JOIN
SELECT
    c.first_name,
    c.last_name,
    o.order_id
FROM customers c
CROSS JOIN orders o
WHERE c.id = o.customer_id;

-- Equivalent to:
SELECT
    c.first_name,
    c.last_name,
    o.order_id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Practical Use Cases

1. Calendar and Date Matrix

-- Generate a daily sales report for every product × every day in January
SELECT
    d.date_value,
    p.product_name,
    COALESCE(SUM(s.quantity), 0) AS total_sold
FROM (
    SELECT DATE '2024-01-01' + n AS date_value
    FROM generate_series(0, 30) AS n
) d
CROSS JOIN products p
LEFT JOIN sales s
    ON s.sale_date = d.date_value
   AND s.product_id = p.product_id
GROUP BY d.date_value, p.product_name
ORDER BY d.date_value, p.product_name;

2. Comparison Reports

-- Compare each employee's salary against every other employee
SELECT
    e1.first_name AS employee1,
    e1.salary AS salary1,
    e2.first_name AS employee2,
    e2.salary AS salary2,
    e1.salary - e2.salary AS difference
FROM employees e1
CROSS JOIN employees e2
WHERE e1.employee_id < e2.employee_id
  AND ABS(e1.salary - e2.salary) > 10000
ORDER BY difference DESC;

3. Pivot-Style Reports

-- Generate a month × product matrix with zeros for empty cells
SELECT
    m.month_name,
    p.product_name,
    COALESCE(SUM(o.total_amount), 0) AS revenue
FROM (
    SELECT 'January' AS month_name, 1 AS month_num UNION ALL
    SELECT 'February', 2 UNION ALL
    SELECT 'March', 3
) m
CROSS JOIN products p
LEFT JOIN orders o
    ON MONTH(o.order_date) = m.month_num
   AND o.product_id = p.product_id
GROUP BY m.month_name, p.product_name
ORDER BY m.month_num, p.product_name;

Performance Considerations

-- Always check result size before executing
SELECT COUNT(*) FROM table_a;  -- M rows
SELECT COUNT(*) FROM table_b;  -- N rows
-- Result will be M × N — proceed with caution

Practice Exercises

Exercise 1: Generate all combinations of 3 sizes and 5 colors.

-- Solution
SELECT
    s.size_name,
    c.color_name
FROM sizes s
CROSS JOIN colors c
ORDER BY s.size_name, c.color_name;
-- Expected: 15 rows

Exercise 2: Create a report showing every product alongside every month of 2024, with zero sales where no data exists.

-- Solution
SELECT
    p.product_name,
    m.month_name,
    COALESCE(SUM(o.total_amount), 0) AS monthly_revenue
FROM products p
CROSS JOIN (
    SELECT 'Jan' AS month_name, 1 AS m UNION ALL SELECT 'Feb', 2 UNION ALL
    SELECT 'Mar', 3 UNION ALL SELECT 'Apr', 4 UNION ALL
    SELECT 'May', 5 UNION ALL SELECT 'Jun', 6
) m
LEFT JOIN orders o
    ON o.product_id = p.product_id
   AND MONTH(o.order_date) = m.m
GROUP BY p.product_name, m.month_name, m.m
ORDER BY p.product_name, m.m;

Exercise 3: Generate a test dataset assigning every role to every user.

-- Solution
INSERT INTO user_roles (user_id, role_id, assigned_at)
SELECT
    u.user_id,
    r.role_id,
    NOW()
FROM users u
CROSS JOIN roles r
WHERE NOT EXISTS (
    SELECT 1 FROM user_roles ur
    WHERE ur.user_id = u.user_id
      AND ur.role_id = r.role_id
);

See Also

• INNER JOIN
• LEFT JOIN
• FULL OUTER JOIN
• Self JOIN
• UNION, INTERSECT, EXCEPT