How to write SQL CROSS JOINs and why they are so dangerous
Structured Query Language (SQL) provides several types of joins to combine data from multiple tables. Among these, the CROSS JOIN is arguably the most misunderstood and the most dangerous when used without caution.
Learn SQL online
In this article, we’ll explore:
What a CROSS JOIN is and how it works
How to write and interpret CROSS JOINs
Real-world examples with output
Why CROSS JOINs can be hazardous in practice
When it’s appropriate to use a CROSS JOIN
Best practices to avoid costly mistakes
Let’s dive into the world of Cartesian products in SQL.
What is a SQL CROSS JOIN?
A CROSS JOIN returns the Cartesian product of two tables. This means:
Every row from the first table is paired with every row from the second table, without any condition filtering the join.
Think of it as forming all possible combinations of the rows from both tables. It’s a brute-force operation and can scale in size very quickly.
Mathematical Definition
If:
Table A has
mrowsTable B has
nrows
Then:
The result of
A CROSS JOIN Bwill havem × nrows.
This is computationally expensive and should only be done intentionally.
How to Write a CROSS JOIN
There are two syntaxes you can use to write a CROSS JOIN:
Explicit CROSS JOIN
SELECT *
FROM table_a
CROSS JOIN table_b;
This form is clear and communicates the intent unambiguously.
Implicit CROSS JOIN (Comma Syntax)
SELECT *
FROM table_a, table_b;
This older, comma-separated form of join syntax is functionally identical to a CROSS JOIN if no WHERE clause is provided. Unfortunately, this is where many accidental CROSS JOINs come from, especially when join conditions are forgotten.
Example: CROSS JOIN in Action
Let’s use two simple tables to demonstrate:
Table: Colors
| id | color |
|---|---|
| 1 | Red |
| 2 | Green |
| 3 | Blue |
Table: Sizes
| id | size |
|---|---|
| A | Small |
| B | Medium |
| C | Large |
Now let’s run a CROSS JOIN between colors and sizes.
Query:
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;
Output:
| color | size |
|---|---|
| Red | Small |
| Red | Medium |
| Red | Large |
| Green | Small |
| Green | Medium |
| Green | Large |
| Blue | Small |
| Blue | Medium |
| Blue | Large |
This result includes every combination of color and size: 3 colors × 3 sizes = 9 rows.
Why CROSS JOINs Are Dangerous
Despite their simplicity, CROSS JOINs are one of the most dangerous tools in SQL. Here’s why.
Explosive Row Multiplication
CROSS JOINs scale multiplicatively. This might not be a problem with 3 or 4 rows, but in real-world systems, it can explode quickly.
Imagine joining:
100,000 customers
5,000 products
A CROSS JOIN here would produce:
100,000 × 5,000 = 500,000,000rows.
If your database tries to materialize that in memory, you could:
Crash the query engine
Exhaust server memory
Cause serious performance degradation
They’re Easy to Write by Accident
Here’s a classic mistake:
SELECT *
FROM employees, departments;
If the developer forgets to include a join condition (like ON employees.department_id = departments.id), this query becomes a CROSS JOIN, pairing every employee with every department. In large organizations, this mistake could create millions of unnecessary rows.
Hard to Debug and Understand
CROSS JOINs produce a lot of noise. Unless you specifically need a full pairing, the result set often contains meaningless or invalid combinations that pollute the data and confuse users.
Even worse, because the syntax of a CROSS JOIN can look like a regular join (especially in the implicit form), it may take a while before anyone notices something is wrong.
When to Use CROSS JOINs Intentionally
While most use cases don’t require a CROSS JOIN, there are some legitimate scenarios where it is the right tool.
Generating All Combinations
If you’re building a product catalog, testing system, or grid of all possibilities, CROSS JOIN is perfect.
Example:
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;
his gives all combinations of colors and sizes, great for inventory planning or UI dropdown generation.
Creating Time/Slot Grids
Suppose you’re building a scheduling app where each day should offer multiple time slots.
Example:
SELECT d.date, t.slot
FROM calendar_days d
CROSS JOIN time_slots t;
This gives every day/slot combination for a calendar view.
Synthetic Data Generation
Need to populate a table with test data or simulate a data environment?
CROSS JOIN can create combinations that simulate realistic workloads.
Example:
SELECT users.name, test_cases.id
FROM users
CROSS JOIN test_cases;
Another useful operator is BETWEEN, which is great for checking if a value falls within a certain range. For instance, if you’re interested in employees who were hired between 2016 and 2019, you might write:
SELECT *
FROM employees
WHERE hire_date BETWEEN '2016-01-01' AND '2019-12-31';
Now every user is assigned every test case—useful for load testing or algorithm trials.
Best Practices to Avoid CROSS JOIN Pitfalls
Always Use Explicit JOINs
UseINNER JOIN,LEFT JOIN, etc., withONconditions to make join logic clear.Avoid Comma-Separated FROM Clauses
Especially in legacy code or older SQL tutorials.Add LIMIT Clauses in Development
While testing, always useLIMIT 10or similar to inspect the output before running full queries.Check Execution Plans
Most SQL engines (PostgreSQL, MySQL, SQL Server, etc.) let you inspect the query plan. If you see nested loops or massive estimates, recheck your joins.Monitor Query Size
Especially in ETL pipelines or analytics workflows, monitor intermediate table sizes to prevent runaway joins.Consider Row Count Beforehand
A quick count can warn you:
SELECT COUNT(*) FROM a; -- 10,000
SELECT COUNT(*) FROM b; -- 25,000
-- Do you really want 250 million rows?
Summary
CROSS JOINs are a double-edged sword in SQL:
| ✅ Pros | ❌ Cons |
|---|---|
| Generates full combinations | Can create huge, unmanageable result sets |
| Useful for test data | Easy to write by accident |
| Ideal for calendar/data grids | Difficult to debug if misused |
Used deliberately, CROSS JOINs can be powerful tools in a SQL developer’s toolkit. But if misapplied—or worse, written by mistake—they can become dangerous liabilities that tank performance and flood your system with meaningless data.
Treat CROSS JOINs with caution and respect.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
