SQL Server › Joins

How does a CROSS JOIN work, and what are some legitimate use cases for it despite the risk of accidentally creating a huge result set?

Advanced

Answer

A CROSS JOIN combines every row from the first table with every row from the second table, producing a result set whose size is the product of both table's row counts, which is genuinely useful for generating combinations, such as pairing every product with every available size and color option, but can accidentally produce an enormous result if used unintentionally without a proper join condition.

SELECT p.ProductName, s.SizeName, c.ColorName
FROM Products p
CROSS JOIN Sizes s
CROSS JOIN Colors c;

Real-world example A clothing retailer uses a CROSS JOIN to generate every possible combination of product, size, and color for their inventory system, intentionally creating all the valid product variant combinations needed.

Common follow-ups: How do you accidentally create a CROSS JOIN by mistake, and how do you spot it?;What is a reasonable way to limit the size of a CROSS JOIN's result set?

Data Types & Schema Design;Query Optimization & Plans