CROSS JOIN / LATERAL JOIN

Category: JOIN Operations

Platform Support:

✅ Snowflake (CROSS JOIN, LATERAL) | ✅ BigQuery (CROSS JOIN, CROSS JOIN UNNEST) | ✅ Databricks (CROSS JOIN, LATERAL VIEW)

Description

CROSS JOIN produces a Cartesian product - every row from the first table combined with every row from the second table. LATERAL joins allow correlated subqueries and table functions. Essential for generating combinations, expanding arrays, and complex transformations.

Syntax

CROSS JOIN:

SELECT *
FROM table1
CROSS JOIN table2;

LATERAL (Snowflake):

SELECT *
FROM table1,
LATERAL (
    SELECT * FROM table2 WHERE table2.id = table1.id
) lateral_alias;

CROSS JOIN UNNEST (BigQuery):

SELECT *
FROM table1
CROSS JOIN UNNEST(array_column) as element;

LATERAL VIEW (Databricks):

SELECT *
FROM table1
LATERAL VIEW EXPLODE(array_column) lateral_alias AS element;

Platform-Specific Notes

Snowflake:

• Full CROSS JOIN support
• LATERAL for correlated table functions
• LATERAL FLATTEN for arrays/objects
• Can reference columns from left side

BigQuery:

• CROSS JOIN for Cartesian product
• CROSS JOIN UNNEST for arrays
• No LATERAL keyword needed
• Implicit lateral joins in subqueries

Databricks:

• CROSS JOIN standard
• LATERAL VIEW for table-generating functions
• LATERAL VIEW EXPLODE for arrays
• Compatible with Hive syntax

Example 1: CROSS JOIN - Generate All Combinations

All Platforms:

-- Generate all size-color combinations
SELECT 
    p.product_name,
    s.size,
    c.color,
    CONCAT(p.product_name, ' - ', s.size, ' - ', c.color) as variant_name
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c
ORDER BY p.product_name, s.size, c.color;

Sample Data:

products:

sizes:

colors:

Result (12 combinations = 2 products × 3 sizes × 2 colors):

Example 2: Generate Date Series with CROSS JOIN

All Platforms:

-- Generate all dates and hours for a week
WITH dates AS (
    SELECT DATE '2024-01-15' + INTERVAL (seq - 1) DAY as date
    FROM UNNEST(GENERATE_ARRAY(1, 7)) as seq  -- BigQuery
    -- TABLE(GENERATOR(ROWCOUNT => 7)) as seq  -- Snowflake
    -- EXPLODE(SEQUENCE(1, 7)) as seq  -- Databricks
),
hours AS (
    SELECT seq as hour
    FROM UNNEST(GENERATE_ARRAY(0, 23)) as seq  -- BigQuery
)
SELECT 
    date,
    hour,
    TIMESTAMP(date) + INTERVAL hour HOUR as datetime
FROM dates
CROSS JOIN hours
ORDER BY date, hour;

Result (168 rows = 7 days × 24 hours):

Example 3: LATERAL FLATTEN (Snowflake)

Snowflake:

SELECT 
    o.order_id,
    o.customer_name,
    f.value:product_name::STRING as product_name,
    f.value:quantity::INTEGER as quantity,
    f.value:price::FLOAT as price
FROM orders o,
LATERAL FLATTEN(input => o.items) f;

Sample Data (orders with JSON items):

Result:

Example 4: CROSS JOIN UNNEST (BigQuery)

BigQuery:

SELECT 
    o.order_id,
    o.customer_name,
    item.product_name,
    item.quantity,
    item.price,
    item.quantity * item.price as line_total
FROM orders o
CROSS JOIN UNNEST(o.items) as item;

Result (same as Snowflake example above with added line_total)

Example 5: LATERAL VIEW EXPLODE (Databricks)

Databricks:

SELECT 
    o.order_id,
    o.customer_name,
    item.product_name,
    item.quantity,
    item.price
FROM orders o
LATERAL VIEW EXPLODE(o.items) items_table AS item;

Example 6: Fill Missing Data with CROSS JOIN

All Platforms:

-- Ensure all products have entries for all dates (fill gaps)
WITH date_range AS (
    SELECT date
    FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', '2024-01-07')) as date
),
all_products AS (
    SELECT DISTINCT product_id, product_name
    FROM products
),
all_combinations AS (
    SELECT 
        p.product_id,
        p.product_name,
        d.date
    FROM all_products p
    CROSS JOIN date_range d
)
SELECT 
    ac.product_id,
    ac.product_name,
    ac.date,
    COALESCE(s.sales, 0) as sales
FROM all_combinations ac
LEFT JOIN sales s 
    ON ac.product_id = s.product_id 
    AND ac.date = s.sale_date
ORDER BY ac.product_id, ac.date;

Sample Data:

sales (incomplete - missing dates):

Result (complete - all dates filled):

Example 7: Correlated LATERAL Join (Snowflake)

Snowflake:

-- Get top 3 products for each customer
SELECT 
    c.customer_id,
    c.customer_name,
    top_products.product_name,
    top_products.total_spent,
    top_products.purchase_count
FROM customers c,
LATERAL (
    SELECT 
        p.product_name,
        SUM(o.total) as total_spent,
        COUNT(*) as purchase_count
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.customer_id = c.customer_id
    GROUP BY p.product_name
    ORDER BY SUM(o.total) DESC
    LIMIT 3
) top_products
ORDER BY c.customer_id, top_products.total_spent DESC;

CROSS JOIN Row Count Calculation

Formula: rows_in_result = rows_in_table1 × rows_in_table2 × ... × rows_in_tableN

Example:

• Table1: 1,000 rows
• Table2: 100 rows
• Table3: 10 rows
• Result: 1,000,000 rows

Warning: CROSS JOIN can create very large result sets!

When to Use CROSS JOIN

Good Use Cases:

1. Generate combinations: Product variants, test scenarios
2. Fill missing data: Create complete date/product matrices
3. Expand reference data: Apply lookup to all rows
4. Create scaffolding: Generate row templates
5. Statistical sampling: Create parameter combinations

Avoid When:

• You need only matching rows (use INNER JOIN)
• Result set would be too large
• Better alternatives exist (UNION, window functions)

LATERAL vs Subquery

Common Use Cases

1. Product variants: Generate all size/color/style combinations
2. Time series: Create complete date ranges
3. Test data: Generate test scenarios
4. Array expansion: Flatten nested structures
5. Calendar joins: Match all dates to all entities
6. Parameter grids: ML hyperparameter tuning

Performance Considerations

• CROSS JOIN can be expensive: N × M rows
• Add filters: Reduce result set size
• Index appropriately: Helps with subsequent joins
• Consider alternatives: Sometimes UNION or window functions better
• Test with LIMIT: Preview results before full execution
• Monitor memory: Large cross joins use significant memory

Best Practices

1. Understand row count: Calculate expected result size
2. Add WHERE clauses: Filter early when possible
3. Use LIMIT for testing: Preview results
4. Document intent: Explain why CROSS JOIN is needed
5. Consider performance: Be aware of computational cost
6. Validate results: Ensure combinations make sense
7. Use LATERAL for correlation: More readable than subqueries