What is a CROSS JOIN in SQL?
Learn what a CROSS JOIN is in SQL, how it produces the Cartesian product of two tables, its row-count behavior, use cases, examples and interview questions.
Expected Interview Answer
A CROSS JOIN returns the Cartesian product of two tables, pairing every row from the first table with every row from the second, so the result has rows equal to the product of the two row counts.
Unlike INNER or OUTER joins, a CROSS JOIN has no ON condition because it does not match rows on any key — it simply combines all possible pairs. It is written as SELECT ... FROM A CROSS JOIN B, or implicitly with a comma-separated FROM and no WHERE. It is useful for generating combinations such as sizes and colors, date and store grids, or test data, but can explode in size, so it must be used deliberately.
- Generates every possible combination of rows
- Ideal for building grids like size and color matrices
- Useful for creating calendar or test data scaffolds
- Requires no join key or ON clause
- Simple, explicit way to express a Cartesian product
AI Mentor Explanation
Think of planning a tournament where every team in one group must play every team in another group. CROSS JOIN builds that full fixture list automatically: five home sides paired with five visiting sides gives twenty-five matchups, no key needed, just every combination of one side with every other so nothing is missed.
Step-by-Step Explanation
Step 1
Understand the goal
You want every possible pairing of rows between two tables, not matched by any key.
Step 2
Write the CROSS JOIN
Use SELECT ... FROM A CROSS JOIN B with no ON clause, since there is no matching condition.
Step 3
Predict the row count
The result has rows(A) multiplied by rows(B) rows; check this before running on large tables.
Step 4
Optionally filter
Add a WHERE clause to keep only the combinations you actually need, turning it toward an inner-join result.
Step 5
Use for grids
Apply it to build combinations such as sizes and colors or dates and stores for reporting.
What Interviewer Expects
- Definition as a Cartesian product
- Knowing there is no ON clause
- Ability to compute the result row count
- Awareness of the size explosion risk
- A practical use case such as generating combinations
Common Mistakes
- Confusing CROSS JOIN with INNER JOIN
- Adding an ON clause to a CROSS JOIN
- Accidentally creating one via a comma join with a missing WHERE
- Underestimating the result size on large tables
- Believing CROSS JOIN removes duplicates
Best Answer (HR Friendly)
“A CROSS JOIN pairs every row of one table with every row of another, giving you all possible combinations. It is handy for building grids like every size with every color, but the result grows fast, so you use it carefully.”
Code Example
-- Every size paired with every color
SELECT s.size_name, c.color_name
FROM sizes AS s
CROSS JOIN colors AS c;
-- Implicit cross join via comma (avoid: easy to forget WHERE)
SELECT s.size_name, c.color_name
FROM sizes AS s, colors AS c;
-- Combinations kept only where useful, using a filter
SELECT s.size_name, c.color_name
FROM sizes AS s
CROSS JOIN colors AS c
WHERE c.color_name <> 'Discontinued';Follow-up Questions
- How does a CROSS JOIN differ from an INNER JOIN?
- How many rows does a CROSS JOIN of a 10-row and 5-row table produce?
- How can a comma join accidentally become a CROSS JOIN?
- When would a CROSS JOIN be genuinely useful?
- Can you add a WHERE clause to a CROSS JOIN?
MCQ Practice
1. A CROSS JOIN between a table of 4 rows and a table of 3 rows produces how many rows?
A CROSS JOIN returns the Cartesian product, so 4 multiplied by 3 equals 12 rows.
2. Which clause is NOT used with a CROSS JOIN?
A CROSS JOIN has no ON clause because it does not match rows on any key; WHERE can still filter the result.
3. What does a CROSS JOIN return?
It pairs every row of the first table with every row of the second, the Cartesian product.
Flash Cards
What is a CROSS JOIN? — A join that returns the Cartesian product: every row of table A paired with every row of table B.
Does CROSS JOIN use an ON clause? — No. It has no matching condition, so there is no ON clause.
How big is a CROSS JOIN result? — rows(A) multiplied by rows(B) rows.
A common accidental CROSS JOIN? — A comma-separated FROM with no WHERE condition linking the tables.