MySQL CROSS JOIN: Return Every Combination of Rows

MySQL CROSS JOIN returns every possible combination of rows from two tables. It does not require an ON condition because it is not matching rows by a key.

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.product AS sold_product
FROM products AS p
CROSS JOIN sales AS s;

If products has 8 rows and sales has 9 rows, the CROSS JOIN returns:

8 x 9 = 72 rows

CROSS JOIN Syntax Top ↑

SELECT table1.column1,
       table2.column2
FROM table1
CROSS JOIN table2;

Unlike INNER JOIN, LEFT JOIN, and RIGHT JOIN, a CROSS JOIN normally has no ON clause. Its purpose is to create the Cartesian product of the input rows.

Sample Tables Top ↑

The examples use the existing Plus2net products and sales sample tables.

Download sample tables and data

Basic CROSS JOIN: 8 x 9 = 72 Rows Top ↑

The original page contained a typo, SLECT. The correct query is:

SELECT p.p_id,
       p.product AS catalog_product,
       p.price,
       s.sale_id,
       s.p_id AS sale_p_id,
       s.product AS sold_product,
       s.qty,
       s.store
FROM products AS p
CROSS JOIN sales AS s
ORDER BY s.sale_id, p.p_id;

Every product is paired with every sale, so 8 product rows x 9 sales rows = 72 result rows.

Calculate the Number of Result Rows Top ↑

For an unfiltered CROSS JOIN, the theoretical number of output rows is:

rows in table A x rows in table B

You can also ask MySQL to count the combinations:

SELECT COUNT(*) AS combinations
FROM products
CROSS JOIN sales;

With 8 and 9 source rows, combinations is 72.

See MySQL COUNT() for more counting patterns.

CROSS JOIN with WHERE Top ↑

A WHERE clause filters combinations after the Cartesian product is logically formed.

The sample sales table has five rows where qty = 2. Each of those five rows is paired with all eight products:

SELECT p.p_id,
       p.product AS catalog_product,
       s.sale_id,
       s.product AS sold_product,
       s.qty,
       s.store
FROM products AS p
CROSS JOIN sales AS s
WHERE s.qty = 2
ORDER BY s.sale_id, p.p_id;

The result contains:

8 products x 5 qualifying sales = 40 rows

Filter Columns with the Same Name Top ↑

Both sample tables contain p_id and product, so table aliases are important whenever a query refers to one of those columns.

Filter the products table Top ↑

SELECT p.p_id,
       p.product AS catalog_product,
       s.sale_id,
       s.product AS sold_product,
       s.qty
FROM products AS p
CROSS JOIN sales AS s
WHERE p.p_id = 2
ORDER BY s.sale_id;

Only one product has p_id = 2, and it is paired with all 9 sales rows. The result therefore has 9 rows.

Filter the sales table Top ↑

SELECT p.p_id,
       p.product AS catalog_product,
       s.sale_id,
       s.p_id AS sale_p_id,
       s.product AS sold_product,
       s.qty
FROM products AS p
CROSS JOIN sales AS s
WHERE s.p_id = 2
ORDER BY s.sale_id, p.p_id;

Three sales rows have p_id = 2, and each is paired with 8 products:

3 x 8 = 24 rows

View the complete 24-row sample output.

When WHERE Adds a Table Relationship Top ↑

A CROSS JOIN followed by a WHERE condition that relates the two tables can produce the same result as an INNER JOIN.

For example:

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.qty
FROM products AS p
CROSS JOIN sales AS s
WHERE p.p_id = s.p_id;

is logically equivalent for this equality condition to:

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.qty
FROM products AS p
INNER JOIN sales AS s
  ON p.p_id = s.p_id;

Reduce Rows before the CROSS JOIN Top ↑

When only a small subset from one side is needed, writing the query so that the intended subset is clear can make the Cartesian size easier to reason about.

For example, pair every product with only sales where quantity is 2:

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.qty
FROM products AS p
CROSS JOIN (
    SELECT sale_id,
           qty
    FROM sales
    WHERE qty = 2
) AS s
ORDER BY s.sale_id, p.p_id;

MySQL's optimizer may choose its own efficient execution strategy, but this form makes the intended input set obvious to the reader: 8 product rows crossed with 5 qualifying sales rows.

Practical Uses of CROSS JOIN Top ↑

CROSS JOIN is useful when the application really needs all combinations, for example:

• all shirt sizes combined with all available colors,
• all stores combined with all reporting dates,
• all employees combined with all required training modules,
• all products combined with a short list of scenarios or rate assumptions,
• generating test combinations from small lookup tables.

Example: Size and color combinations Top ↑

SELECT s.size_name,
       c.color_name
FROM sizes AS s
CROSS JOIN colors AS c
ORDER BY s.size_name, c.color_name;

If there are 4 sizes and 5 colors, this creates 20 possible combinations.

CROSS JOIN Three Tables Top ↑

CROSS JOIN can combine more than two tables:

SELECT s.size_name,
       c.color_name,
       m.material_name
FROM sizes AS s
CROSS JOIN colors AS c
CROSS JOIN materials AS m;

If the tables contain 4 sizes, 5 colors and 3 materials, the result contains:

4 x 5 x 3 = 60 combinations

Each additional CROSS JOIN multiplies the possible result size, so row counts should be estimated before using the query on large tables.

CROSS JOIN vs INNER, LEFT and RIGHT JOIN Top ↑

Performance and Safety Top ↑

• Estimate the Cartesian size before running a CROSS JOIN on large tables.
• Remember that result size multiplies: 1,000 x 1,000 already produces 1,000,000 combinations.
• Select only the columns required by the application instead of using SELECT *.
• Apply legitimate filters as early and clearly as possible so the intended input sets are easy to understand.
• If the tables are actually related by keys, use INNER/LEFT/RIGHT JOIN rather than creating a Cartesian product and repairing it later.
• Use LIMIT while inspecting an unfamiliar CROSS JOIN, but do not mistake LIMIT for a fix for an incorrectly large query.
• Use EXPLAIN and actual measurements for important production queries.

Safe inspection while developing Top ↑

SELECT p.p_id,
       p.product,
       s.sale_id
FROM products AS p
CROSS JOIN sales AS s
LIMIT 20;

This lets you inspect a small sample, but the underlying unfiltered cross product still represents all possible combinations.

Common CROSS JOIN Mistakes Top ↑

Using CROSS JOIN accidentally Top ↑

A missing join relationship can produce an unexpectedly huge result. If two tables are related by keys, use an explicit INNER, LEFT, or RIGHT JOIN with an ON condition.

Thinking CROSS JOIN matches common columns automatically Top ↑

It does not. Identically named columns have no special meaning to CROSS JOIN.

Using SELECT * with columns that share names Top ↑

The result can contain repeated column names such as two p_id and two product columns. Select and alias the required columns explicitly.

Forgetting that filters still leave combinations Top ↑

If five sales rows survive a filter and there are eight products, the result still contains 40 combinations.

Using CROSS JOIN + WHERE when INNER JOIN states the relationship better Top ↑

If the WHERE clause is simply p.p_id = s.p_id, write that relationship with INNER JOIN ... ON for clarity.

Assuming LIMIT makes the Cartesian operation conceptually small Top ↑

LIMIT restricts returned rows; it does not change the meaning of the CROSS JOIN. Design the query correctly first.

Video Tutorial Top ↑

Related Tutorials, Samples and Download Top ↑

Download the existing product and sales SQL dump
72-row CROSS JOIN sample
40-row filtered sample
24-row filtered sample

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: