SQL CROSS JOIN

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

CROSS JOIN Syntax

CROSS JOIN is the simplest join syntax. There is no ON clause because every row pairs with every other row. The two syntaxes below are equivalent.

-- Explicit CROSS JOIN (preferred)
SELECT a.color, b.size
FROM colors a
CROSS JOIN sizes b;

-- Implicit CROSS JOIN (comma syntax)
SELECT a.color, b.size
FROM colors a, sizes b;

-- Both produce:
-- | color | size  |
-- |-------|-------|
-- | red   | S     |
-- | red   | M     |
-- | red   | L     |
-- | blue  | S     |
-- | blue  | M     |
-- | blue  | L     |

Three by three is fine. A million by a million is a trillion rows and a Snowflake bill that'll wake up a VP. The math isn't the part that catches people. The part that catches people is that the query runs, slowly at first, then not at all, and nobody notices until the morning report fails to land. We've seen it three times. Use CROSS JOIN on purpose or not at all.

Use Case 1: Date Spines

The most common CROSS JOIN in production data engineering is the date spine. You CROSS JOIN a calendar table with a dimension (like stores, products, or regions) to create a row for every date for every entity. Then you LEFT JOIN your fact table onto this spine to fill in actual values, with zeros or NULLs for dates with no activity.

-- Generate a date spine: one row per date per store
WITH date_spine AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
  )::date AS date
),
stores AS (
  SELECT DISTINCT store_id, store_name
  FROM sales
)
SELECT
  d.date,
  s.store_id,
  s.store_name,
  COALESCE(f.daily_revenue, 0) AS daily_revenue,
  COALESCE(f.order_count, 0) AS order_count
FROM date_spine d
CROSS JOIN stores s
LEFT JOIN (
  SELECT
    sale_date,
    store_id,
    SUM(revenue) AS daily_revenue,
    COUNT(*) AS order_count
  FROM sales
  GROUP BY sale_date, store_id
) f ON d.date = f.sale_date AND s.store_id = f.store_id
ORDER BY d.date, s.store_id;

Without the CROSS JOIN, dates with no sales for a given store would be missing from the result. Dashboards would show gaps in the timeline. The date spine ensures every date appears for every store, and LEFT JOIN fills in zeros for inactive days.

Use Case 2: Generating Combinations

CROSS JOIN creates all possible pairs or combinations from two sets. This is useful for product variant generation, test matrix creation, and comparison analysis.

-- Generate all product variant combinations
SELECT
  c.color_name,
  s.size_label,
  CONCAT(p.product_name, ' - ', c.color_name, ' - ', s.size_label)
    AS variant_name
FROM products p
CROSS JOIN colors c
CROSS JOIN sizes s
WHERE p.category = 'shirts'
ORDER BY p.product_name, c.color_name, s.size_label;

-- Compare every pair of employees (for peer review assignment)
SELECT
  e1.employee_id AS reviewer_id,
  e1.name AS reviewer,
  e2.employee_id AS reviewee_id,
  e2.name AS reviewee
FROM employees e1
CROSS JOIN employees e2
WHERE e1.employee_id != e2.employee_id  -- exclude self-pairs
  AND e1.department = e2.department      -- same department only
ORDER BY e1.name, e2.name;

Performance Dangers

CROSS JOIN is the easiest way to accidentally create a query that brings down a production database. The output grows multiplicatively, and most people underestimate how fast that gets out of hand.

-- BUG: date is not unique in either table
-- 50 sales rows and 30 expense rows on the same date
-- = 1,500 rows for that one date
SELECT s.*, e.*
FROM daily_sales s
JOIN daily_expenses e ON s.date = e.date;

-- FIX: aggregate first, then join on the unique grain
WITH agg_sales AS (
  SELECT date, SUM(amount) AS total_sales
  FROM daily_sales GROUP BY date
),
agg_expenses AS (
  SELECT date, SUM(amount) AS total_expenses
  FROM daily_expenses GROUP BY date
)
SELECT s.date, s.total_sales, e.total_expenses
FROM agg_sales s
JOIN agg_expenses e ON s.date = e.date;

3 CROSS JOIN Interview Questions

CROSS JOIN questions are less common than INNER or LEFT JOIN questions, but when they appear, candidates who know the pattern stand out.

SQL CROSS JOIN FAQ

Related Guides