• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL CROSS JOIN

← Previous Next →

In relational databases, there are situations where you want to see all possible combinations between two tables. For example, you may want to pair every customer with every product for analysis or testing purposes.

The MySQL CROSS JOIN is used to produce the Cartesian product of two tables. Unlike other joins, CROSS JOIN does not require any matching columns between the tables. Every row from the first table is combined with every row from the second table.

What Is a CROSS JOIN in MySQL?

A CROSS JOIN returns all possible combinations of rows from two or more tables.

  • If the first table has m rows and the second table has n rows, the result will contain m × n rows.

  • There is no condition or ON clause required.

  • CROSS JOIN is rarely used in real applications but can be helpful for generating combinations, test data, or schedules.

CROSS JOIN Syntax

The basic syntax of a CROSS JOIN is:

SELECT columns
FROM table1
CROSS JOIN table2;

You can also use a simpler form without the keyword CROSS JOIN:

SELECT columns
FROM table1, table2;

Note: Using the comma-separated method is equivalent to a CROSS JOIN, but using the explicit CROSS JOIN is clearer and more readable.

Example Tables

Let’s take two simple tables — customers and products.

customers

customer_id customer_name
1 Ananya
2 Priya
3 Isha

products

product_id product_name
101 Laptop
102 Headphones
103 Keyboard

CROSS JOIN Example

To display all possible combinations of customers and products:

SELECT c.customer_name, p.product_name
FROM customers c
CROSS JOIN products p;

Result:

customer_name product_name
Ananya Laptop
Ananya Headphones
Ananya Keyboard
Priya Laptop
Priya Headphones
Priya Keyboard
Isha Laptop
Isha Headphones
Isha Keyboard

Explanation

  • There are 3 customers and 3 products.

  • The result contains 3 × 3 = 9 rows.

  • Every customer is paired with every product.

Using Table Aliases

Using aliases makes queries easier to read:

SELECT c.customer_name, p.product_name
FROM customers AS c
CROSS JOIN products AS p;

Aliases c and p simplify referencing columns and are especially useful in larger queries.

CROSS JOIN Without the Keyword

You can achieve the same result using a comma-separated list of tables:

SELECT c.customer_name, p.product_name
FROM customers c, products p;

Note:
While this produces the same Cartesian product, the explicit CROSS JOIN is clearer and less likely to be confused with other types of joins.

CROSS JOIN With WHERE Clause

You can filter the Cartesian product using a WHERE clause. For example, to display only combinations where the product is “Laptop”:

SELECT c.customer_name, p.product_name
FROM customers c
CROSS JOIN products p
WHERE p.product_name = 'Laptop';

Result:

customer_name product_name
Ananya Laptop
Priya Laptop
Isha Laptop

Explanation:
The CROSS JOIN produces all combinations first, then the WHERE clause filters the result.

CROSS JOIN With Multiple Tables

You can perform a CROSS JOIN with more than two tables. Suppose we also have a colors table:

colors

color
Red
Blue

To get every combination of customer, product, and color:

SELECT c.customer_name, p.product_name, co.color
FROM customers c
CROSS JOIN products p
CROSS JOIN colors co;

Result:

customer_name product_name color
Ananya Laptop Red
Ananya Laptop Blue
Ananya Headphones Red
Ananya Headphones Blue
Ananya Keyboard Red
Ananya Keyboard Blue
Priya Laptop Red
Priya Laptop Blue
Priya Headphones Red
Priya Headphones Blue
Priya Keyboard Red
Priya Keyboard Blue
Isha Laptop Red
Isha Laptop Blue
Isha Headphones Red
Isha Headphones Blue
Isha Keyboard Red
Isha Keyboard Blue

Explanation:

  • 3 customers × 3 products × 2 colors = 18 rows.

  • CROSS JOIN multiplies all rows from each table.

Performance Considerations

  • CROSS JOIN can produce very large result sets. For example, joining two tables with 1000 rows each produces 1,000,000 rows.

  • Always use filtering with WHERE clauses when working with large datasets.

  • Avoid using CROSS JOIN unless you intentionally need all combinations.

Common Mistakes to Avoid

  1. Not expecting large result sets:
    Cartesian products can grow rapidly and slow down queries if not filtered.

  2. Using CROSS JOIN instead of INNER JOIN:
    If you intend to join on matching columns, use INNER JOIN instead. CROSS JOIN ignores relationships.

  3. Misusing the comma syntax:
    The comma-separated syntax can be confusing in complex queries; prefer explicit CROSS JOIN for clarity.

When to Use CROSS JOIN

CROSS JOIN is useful when:

  • You need all possible combinations of two or more sets of data.

  • You’re generating test data or schedules.

  • You need to pair every item in one table with every item in another, e.g., pricing all products with all possible options.

Summary of the Tutorial

The MySQL CROSS JOIN produces a Cartesian product of two or more tables. Every row from the first table is combined with every row from the second table.

While it’s rarely used in everyday queries, it is useful for testing, generating combinations, or certain analytical tasks. Understanding CROSS JOIN helps you distinguish when to use it versus INNER, LEFT, or RIGHT JOINs, and ensures you avoid unexpectedly large result sets.

Practice Questions

  1. Write a query to display all possible combinations of customers and products using a CROSS JOIN.

  2. Using CROSS JOIN, generate every combination of employees and departments.

  3. Write a query to show all combinations of students and courses using CROSS JOIN.

  4. Using CROSS JOIN, list all possible pairs of products and colors.

  5. Create a query to display all combinations of cities and weather conditions from cities and weather tables.

  6. Using CROSS JOIN, generate all possible combinations of employees and shifts.

  7. Write a query to display every combination of customers, products, and available discounts using CROSS JOIN with three tables.

  8. Using CROSS JOIN, list all combinations of authors and books for a promotion campaign.

  9. Generate all combinations of teachers and subjects using CROSS JOIN, including teachers with no subjects.

  10. Write a query to display all combinations of movies and genres from movies and genres tables using CROSS JOIN.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top