Beginner

574. CROSS JOIN

Understand how CROSS JOIN creates combinations between every row of two tables.

📘 Lesson 574 of 728 ⏱ 12 min 🎯 Intermediate

A CROSS JOIN returns the Cartesian product of two tables. Every row from the first table is combined with every row from the second table.

Although CROSS JOINs can create very large result sets, they are useful when every possible combination is required.

Generate every possible combination of products and store locations.

SQL 
SELECT
    p.ProductName,
    s.StoreName
FROM Products p
CROSS JOIN Stores s;

Used for scenario generation, product-location combinations, scheduling, testing, and planning.

  • CROSS JOIN has no matching condition.
  • Every row is combined with every other row.
  • The result can become very large.
  • Useful for generating combinations.
  • Use carefully with large tables.
What does CROSS JOIN return?

It returns every possible combination of rows from the two tables.

Why can CROSS JOIN be expensive?

The result size is the product of the row counts of both tables.

Have you finished this lesson?

Introduction to SQL – All Lessons