Beginner

640. CROSS JOIN with Filter

Use filters with CROSS JOIN results to keep only valid combinations.

📘 Lesson 640 of 728 ⏱ 14 min 🎯 Advanced

A CROSS JOIN can first generate combinations and then apply conditions to retain only combinations that meet business rules.

Filtering can make a generated combination set useful while reducing unnecessary rows.

Generate product-region combinations only for active regions.

SQL 
SELECT
    p.ProductName,
    r.RegionName
FROM Products p
CROSS JOIN Regions r
WHERE r.IsActive = 1;

Used in planning, forecasting, pricing matrices, scheduling, and scenario analysis.

  • CROSS JOIN creates combinations.
  • WHERE can filter the resulting combinations.
  • Filtering does not change the basic Cartesian behavior.
  • Apply meaningful conditions to control result size.
  • For relationship-based matching, use an appropriate JOIN instead.
Can CROSS JOIN be filtered?

Yes. A WHERE clause can filter the generated combinations.

When should you avoid CROSS JOIN?

Avoid it when you actually need relationship-based matching or when the generated combinations would be unnecessarily large.

Have you finished this lesson?

Introduction to SQL – All Lessons