1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL CROSS JOIN Explained: Every Combination, With Real Examples

SQL CROSS JOIN pairs every row of one table with every row of another. That sounds dangerous, and it can be, but it is also the cleanest way to build product variants, fill gaps in reports and find missing data. Learn the row math, real uses and the performance risks, with tested queries.

8 min read
SQL CROSS JOIN: every combination with examples

Key takeaways

  • A SQL CROSS JOIN returns the Cartesian product: every row of the first table combined with every row of the second. It has no ON clause.
  • Row count multiplies. 2 colours × 3 sizes gave 6 rows; 1,000 × 1,000 gave 1,000,000.
  • Its best use is building a complete grid, such as every store for every day, then LEFT JOIN real data onto it so gaps show as zero.
  • A comma between tables with a forgotten WHERE is an accidental cross join, and a classic cause of slow queries and wrong totals.

Most joins match rows: an order with its customer, a product with its category. A SQL CROSS JOIN does not match anything. It combines every row with every row. Developers often learn it as a warning (“this is what happens when you forget the join condition”), but it solves several real problems more cleanly than any alternative.

All queries below ran on SQLite 3.50.4. CROSS JOIN is standard SQL, so the syntax is the same in PostgreSQL, MySQL, SQL Server and Oracle. If you need a refresher on joins in general, see our SQL joins guide.

What does CROSS JOIN do?

In maths, the Cartesian product of two sets is every possible ordered pair. CROSS JOIN applies that to tables. If table A has M rows and table B has N rows, the result has M × N rows, and each row contains all columns from both sides. There is nothing to match, so there is no join condition.

A first example: product variants

CREATE TABLE sizes(size TEXT);
INSERT INTO sizes VALUES ('S'), ('M'), ('L');
CREATE TABLE colors(color TEXT);
INSERT INTO colors VALUES ('Black'), ('White');

SELECT color, size
FROM colors CROSS JOIN sizes
ORDER BY color, size DESC;
color size
Black S
Black M
Black L
White S
White M
White L

Every T-shirt variant, generated in one query. Add a third table of fabrics and you get every colour, size and fabric combination. Shops use exactly this to create SKUs.

A small aside on ordering: size DESC happened to give S, M, L here only because the letters sort that way in reverse. For real size order, add a sort column. Our ORDER BY guide covers custom ordering.

The row math

SELECT (SELECT count(*) FROM colors) AS c,
       (SELECT count(*) FROM sizes)  AS s,
       (SELECT count(*) FROM colors CROSS JOIN sizes) AS product;
-- c | s | product
-- 2 | 3 | 6

This multiplication is the most important thing to remember. It grows fast. We cross joined a 1,000 row number series with itself and counted the result: exactly 1,000,000 rows. A 3,000 row table joined to itself produced 9,000,000 rows. SQLite counted those in 0.13 seconds on our machine, but if those rows had to be sorted, sent over a network or written to a report, the cost would be very different.

Three ways to write a cross join

SELECT count(*) FROM colors CROSS JOIN sizes;   -- 6
SELECT count(*) FROM colors, sizes;             -- 6
SELECT count(*) FROM colors JOIN sizes ON 1=1;  -- 6

All three returned 6. The explicit CROSS JOIN keyword is the clearest, because it tells the next reader that the product is intentional. The comma form is older syntax. It is not wrong, but in a long query it is hard to tell whether the missing condition was deliberate. Prefer CROSS JOIN in new code.

Filling report gaps with a calendar grid

This is the most valuable use. Suppose you report daily sales per store:

CREATE TABLE stores(store TEXT);
INSERT INTO stores VALUES ('Pune'), ('Delhi');
CREATE TABLE days(d TEXT);
INSERT INTO days VALUES ('2026-11-14'), ('2026-11-15'), ('2026-11-16');
CREATE TABLE sales(store TEXT, d TEXT, amount INT);
INSERT INTO sales VALUES ('Pune','2026-11-14',500), ('Pune','2026-11-16',300),
                         ('Delhi','2026-11-15',700);

A plain GROUP BY on sales returns only three rows, because days with no sales simply do not exist in the table. Charts then skip those days and averages come out too high. Build the full grid first, then attach the data:

SELECT st.store, dy.d, COALESCE(SUM(s.amount), 0) AS total
FROM stores st
CROSS JOIN days dy
LEFT JOIN sales s ON s.store = st.store AND s.d = dy.d
GROUP BY st.store, dy.d
ORDER BY st.store, dy.d;
store d total
Delhi 2026-11-14 0
Delhi 2026-11-15 700
Delhi 2026-11-16 0
Pune 2026-11-14 500
Pune 2026-11-15 0
Pune 2026-11-16 300

Six rows, one per store per day, with honest zeros. COALESCE turns the NULLs from days without sales into 0, as explained in our COALESCE guide. This grid pattern is also the fix for the gap problem we describe in our LAG and LEAD guide, where a missing month makes “previous row” comparisons span two months.

Finding missing data

The same grid answers “which combinations have no record?”:

SELECT st.store, dy.d
FROM stores st CROSS JOIN days dy
WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.store = st.store AND s.d = dy.d)
ORDER BY 1, 2;
Delhi | 2026-11-14
Delhi | 2026-11-16
Pune  | 2026-11-15

Use it to find stores that did not upload a daily file, employees missing a timesheet, or test environments missing a configuration value. It turns a vague worry into an exact list. Our EXISTS vs IN guide explains the NOT EXISTS part.

Pairs and round-robin fixtures

A table cross joined with itself gives every ordered pair, including each row with itself. Add a filter to keep each pair once:

SELECT p1.name AS home, p2.name AS away
FROM players p1 CROSS JOIN players p2
WHERE p1.name < p2.name;
A | B
A | C
A | D
B | C
B | D
C | D

Four players produced 16 raw pairs, and the < filter kept the 6 unique matches, which is n × (n − 1) / 2. Use <> instead to keep both home and away games. This is technically a self join, covered in our self join guide, built from a cross join plus a filter.

Generating test data and test matrices

Testers can use CROSS JOIN to produce every combination of parameters, for example browsers × operating systems × languages, or user roles × permissions. That gives an exhaustive matrix in one query. Because exhaustive matrices grow so fast, teams usually reduce them with pairwise testing, which keeps every pair of values but far fewer rows. CROSS JOIN is also a quick way to multiply a small seed table into thousands of rows for load tests. See our test data generation guide for more techniques.

Accidental cross joins

The dangerous version looks innocent:

SELECT o.id, c.name
FROM orders o, customers c;   -- forgot WHERE o.customer_id = c.id

With 10,000 orders and 5,000 customers, that returns 50 million rows. Worse, if it hides inside a larger query with SUM, it silently multiplies totals instead of failing. Warning signs:

  • A query that suddenly returns far more rows than either table.
  • Totals that are exact multiples of the right answer.
  • A join condition that compares the wrong columns, so it is always true.

Using explicit JOIN ... ON syntax everywhere makes a missing condition easier to spot in review. PostgreSQL and SQL Server reject JOIN without ON as a syntax error, but do not rely on that: MySQL and SQLite accept it. In our SQLite test, SELECT count(*) FROM a JOIN b on tables of 2 and 3 rows quietly returned 6.

Performance tips

  • Cross join small tables: dimension lists, calendars, status codes. Avoid it between two large fact tables.
  • Filter each side before the join, in a CTE or subquery, to shrink the product.
  • Generate calendars with a recursive CTE or a permanent calendar table instead of large ad hoc lists. Our CTE guide shows the recursive form.
  • Check the row estimate with EXPLAIN before running a cross join on unfamiliar data, as covered in our query optimisation guide.

Tip: before running a cross join, multiply the two row counts in your head. If the answer has more than six digits, stop and check that you really need every combination.

CROSS JOIN vs INNER JOIN

CROSS JOIN INNER JOIN
Join condition None Required ON
Rows returned M × N Only matching pairs
Typical use Combinations, grids, matrices Related records
Risk Row explosion Missing rows if data does not match

An inner join is conceptually a cross join followed by a filter on the condition. Databases do not actually build the full product for an inner join; they use indexes and smarter algorithms. That is why you should always write the condition in ON rather than relying on a later WHERE in comma syntax.

Final thoughts

A SQL CROSS JOIN is a precise tool: it gives you every combination, nothing more and nothing less. Use it on purpose for product variants, complete report grids, missing data checks and test matrices, and write the keyword explicitly so others know it is intentional. Keep the inputs small, multiply the row counts before you run it, and you get one of the most useful patterns in SQL without its famous downside.

FAQ

What does CROSS JOIN do in SQL?

It returns the Cartesian product of two tables, pairing every row of the first with every row of the second, without any join condition.

How many rows does a CROSS JOIN return?

The row counts of the two tables multiplied together. Two rows times three rows gives six, and 1,000 times 1,000 gives one million.

Is a comma join the same as CROSS JOIN?

Yes. Listing tables separated by commas with no WHERE condition produces the same Cartesian product. The CROSS JOIN keyword makes the intent clearer.

When should I use CROSS JOIN?

Use it to build every combination of small lists, such as product variants or a store and date grid for reports, and to find combinations with missing data.

What is the difference between CROSS JOIN and FULL OUTER JOIN?

A full outer join matches rows on a condition and keeps unmatched rows from both sides. A cross join has no condition and pairs every row with every row.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Software Development

Python Deepcopy vs Copy: Fix Nested Lists

Python deepcopy vs shallow copy explained with tested code: aliasing, nested lists, the [[0]*3]*3 trap, memo and cycles, custom __deepcopy__,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
Software Development

Base64 Encoding Explained, With Python Code

Base64 encoding turns bytes into 64 safe text characters. See the bit-level steps, padding, URL-safe base64, the 33% size cost,…

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
Software Development

JavaScript Classes: Private Fields and Extends

JavaScript classes explained with tested Node.js code: constructors, methods, #private fields, getters, static members, extends and super, plus the errors…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →