What is a CROSS JOIN and when is it useful? | Interview Question

Q: What is a CROSS JOIN and when is it useful?

**Target Audience:** Beginners who have seen INNER and LEFT JOIN and want to know what the "join with no condition" is for, plus candidates who want the engine details. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A T-shirt shop sells three sizes (S, M, L) and three colors (Black, Navy, White). The owner wants a price board listing every shirt you can buy. Nobody types nine lines by hand: you take the list of sizes, the list of colors, and write down every possible pairing. Black-S,

The problem

Question presented to candidate: "What does a CROSS JOIN return? Most people say you should never use one — can you give me a case where it is actually the right tool, and tell me what can go wrong with it?"

What a strong answer should cover:

A CROSS JOIN returns the Cartesian product: every row of the left input paired with every row of the right, so the row count is n × m, with no join condition
Legitimate uses: generating combinations (sizes × colors), building a complete grid (stores × months) to LEFT JOIN real data onto so missing cells show as 0, and attaching a one-row result (a grand total, a config row) to every row
Equivalent spellings: FROM a, b and JOIN ... ON TRUE; the danger is an accidental product from a forgotten join condition
If either side is empty the result is empty; use LEFT JOIN ... ON TRUE to keep the left rows
Engine details: PostgreSQL rejects CROSS JOIN ... ON and a bare JOIN without ON, SQLite accepts both, and SQLite never reorders the two sides of a CROSS JOIN

Code / implementation expected: Yes — a combinations query and, ideally, a calendar or grid query that fills gaps.

Schema used by the examples:

joins

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have seen INNER and LEFT JOIN and want to know what the "join with no condition" is for, plus candidates who want the engine details. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A T-shirt shop sells three sizes (S, M, L) and three colors (Black, Navy, White). The owner wants a price board listing every shirt you can buy. Nobody types nine lines by ha

The solution is hidden so you can try first

12 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLEvery product variant: 3 sizes × 3 colors = 9 rows

SQLGap filling: a complete store × month grid, with 0 where there were no sales

SQLAttach a one-row grand total to every row to compute a share

PostgreSQL: cross join with generate_series instead of a months table

SELECT st.name AS store, to_char(m, 'YYYY-MM') AS month,
       COALESCE(SUM(sa.amount), 0) AS revenue
FROM stores AS st
CROSS JOIN generate_series(DATE '2024-01-01', DATE '2024-04-01', INTERVAL '1 month') AS m
LEFT JOIN sales AS sa
  ON sa.store_id = st.id
 AND sa.month = to_char(m, 'YYYY-MM')
GROUP BY st.id, st.name, m
ORDER BY st.id, m;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.