← Back to Chapters

MySQL CROSS JOIN

? MySQL CROSS JOIN

? Quick Overview

The CROSS JOIN keyword returns the Cartesian product of two or more tables — every row from the first table combined with every row from the second table. If table1 has n rows and table2 has m rows, the result set contains n × m rows. Useful for generating combinations or test data, but use carefully (can grow very large).

? Key Concepts

  • Cartesian product: All possible pairs of rows between tables.
  • No join condition: CROSS JOIN does not use ON — it produces combinations.
  • Growth: Result size multiplies; small tables are safe, large tables can explode in size.
  • Use cases: test data generation, combinatorics, pairing lists.

? Syntax / Theory

? View Code Example 
-- Syntax for CROSS JOIN (returns Cartesian product)
SELECT columns
FROM table1
CROSS JOIN table2;

? Code Example(s)

? View Code Example 
-- Get all possible combinations of products and suppliers
SELECT products.product_name, suppliers.supplier_name
FROM products
CROSS JOIN suppliers;
? View Code Example 
-- Get all possible combinations of employees and departments
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

? Live Output / Explanation

What to expect

If products has 4 rows and suppliers has 3 rows, the CROSS JOIN query above returns 12 rows — every product paired with every supplier. There is no filtering; include WHERE if you want to trim results after forming the Cartesian product.

Example result shape

  • product_name: "Pen" — paired with supplier A, B, C
  • product_name: "Notebook" — paired with supplier A, B, C
  • ...and so on (4 × 3 = 12 rows)

✅ Tips & Best Practices

  • Be cautious — CROSS JOIN can produce extremely large result sets when tables are large.
  • Use it intentionally for combinations; for matching related rows prefer INNER JOIN or LEFT JOIN.
  • If you only need combinations of a few columns or generated values, consider using small derived tables or VALUES to limit growth.

? Try It Yourself / Practice Tasks

  • Write a query to generate all combinations of products and customers.
  • Use CROSS JOIN to combine a list of courses with students (small sample).
  • Compare results of a CROSS JOIN vs. an INNER JOIN on similar tables to see differences.