CROSS JOIN: The Only Way to Make Zero Rows Appear

Your chart looks beautifully smooth. Because the months with no revenue quietly fell off the axis.

The problem

A report of “how many exams each student sat in each subject”. If Ann never sat Physics, that row does not exist in the data — so it disappears from the report instead of showing a zero.

The fix: build the frame first, fill it second

SELECT
  s.name,
  sub.name AS subject,
  COUNT(e.id) AS exams
FROM students s
CROSS JOIN subjects sub
LEFT JOIN exams e
       ON e.student_id = s.id
      AND e.subject_id = sub.id
GROUP BY s.name, sub.name
ORDER BY s.name, sub.name;

Three steps: CROSS JOIN creates every cell of the output → LEFT JOIN pours data into the cells that have any → COUNT returns 0 for the empty ones.

Common mistakes

1. Forgetting the join condition — row explosion

-- 10,000 × 5,000 = 50 million rows
SELECT * FROM customers, orders;

A comma between two tables is a CROSS JOIN. This is the most common way to take down a database server entirely by accident.

2. Using CROSS JOIN where INNER JOIN was meant

CROSS JOIN is only correct when you actually want every combination. For related data, always spell out ON.

The second use: generating a continuous date range

WITH days AS (
  SELECT generate_series('2026-01-01'::date,
                         '2026-01-31'::date, '1 day') AS day
)
SELECT d.day, COALESCE(SUM(o.total), 0) AS revenue
FROM days d
LEFT JOIN orders o ON o.order_date::date = d.day
GROUP BY d.day
ORDER BY d.day;

Every day appears; days with no sales show 0. This is the same reason Power BI insists you build a dedicated date table.

Practice

• LeetCode 1280 — Students and Examinations
• LeetCode 1789 — Primary Department for Each Employee
• LeetCode 1350 — Students With Invalid Departments
• HackerRank — Draw The Triangle 1 and 2