E-commerce sites use CROSS JOIN to generate product variants: one product (T-Shirt) × three sizes (S, M, L) = three SKUs. Scheduling systems use it to create time slots: 7 days × 24 hours = 168 time slots. Reporting tools generate date ranges: 12 months × 4 quarters = 48 period combinations. Test data generation: 10 test users × 5 test scenarios = 50 test cases. Calendar tables: dates × business rules. CROSS JOIN is the "combination generator" - use it when you need every possible pairing, but be careful: 1000 rows × 1000 rows = 1 million rows!

Automated SKU Generation for Product Variants

An online clothing retailer sells products in multiple sizes and colors. They need to automatically generate SKUs (Stock Keeping Units) for every possible product-size-color combination to manage inventory efficiently.

E-commerce

-- Automated SKU generation using CROSS JOIN

INSERT INTO product_variants (product_id, size_id, color_id, sku, price)

SELECT

p.id AS product_id,

s.id AS size_id,

c.id AS color_id,

CONCAT(

UPPER(REPLACE(p.name, ' ', '')), '-',

s.code, '-',

c.code

) AS sku,

p.base_price + s.price_modifier + c.price_modifier AS price

FROM products p

CROSS JOIN sizes s

CROSS JOIN colors c

WHERE p.category = 'Clothing'

AND p.active = true

AND s.available_for_category = 'Clothing'

AND c.available_for_category = 'Clothing';

-- Result: Automatically generates all valid combinations

-- 50 products × 5 sizes × 8 colors = 2,000 SKUs in seconds

-- No manual work, no missed combinations

Reduced SKU creation time from days to seconds. Eliminated human errors in variant generation. Ensured complete product catalog coverage. Enabled rapid product launches with all size-color combinations ready instantly.

All

Hospital Staff Scheduling Matrix

A hospital needs to create a complete scheduling matrix for the next month, showing all date-shift combinations. This ensures no time slot is accidentally left unscheduled and helps identify coverage gaps.

Healthcare

-- Complete scheduling matrix with CROSS JOIN

WITH date_range AS (

SELECT DATE('2025-02-01') + INTERVAL (n) DAY AS date_value

FROM (

SELECT a.N + b.N * 10 + c.N * 100 AS n

FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a

CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) b

CROSS JOIN (SELECT 0 AS N) c

) numbers