CROSS JOIN in SQL

CROSS JOIN pairs every row from the first table with every row from the second — a Cartesian product. No ON condition, no concept of "matching."

Reference Tables Used in This Section

Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.

employees

departments

Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.

Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Example

SELECT e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

Row count: 4 employees × 3 departments = 12 rows — every possible combination, whether or not it makes real-world sense:

Practical Use Case

CROSS JOIN is intentionally used when you genuinely need every combination — generating a full calendar of (date × store) combinations for a sales report so every store shows up on every date even with zero sales, building a set of (size × color) product variants, or creating test data.

-- Every store, every day of the month — even days with 0 sales
SELECT s.store_name, d.calendar_date
FROM stores s
CROSS JOIN calendar_dates d;

Common Mistakes

• The most common CROSS JOIN in production is accidental. Writing FROM employees e, departments d with no WHERE/ON condition silently produces a Cartesian product — this is one of the most common real-world causes of a query that "returns way too many rows" or a report with duplicated totals.
• Running an unintentional CROSS JOIN on two large tables — row counts multiply, not add, so this can produce millions of rows and hang a query without warning

Interview Relevance

Q: "What happens if you join two tables without an ON clause?" — the answer is a Cartesian product / implicit cross join, and being able to explain why that's usually a bug (not intentional CROSS JOIN) shows real debugging experience.

Practice Question

If products has 20 rows and colors has 5 rows, how many rows does products CROSS JOIN colors return?

Related SQL Notes

• SQL Joins Overview
• INNER JOIN