SQL CROSS JOIN: Cartesian Products

-- 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     |

Always use the explicit syntax. Writing CROSS JOIN tells every reader 'yes, I intended the Cartesian product.' The comma syntax looks like a regular FROM clause with a missing WHERE, which makes code reviewers wonder if you forgot the join condition.

-- 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;

Interview note: Date spine generation is one of the most-tested patterns in data engineering interviews. If an interviewer asks you to 'fill in missing dates' or 'show all days including days with zero activity,' they want a CROSS JOIN with a calendar table followed by a LEFT JOIN to the fact table.

-- 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;

-- 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;