INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN

Category: JOIN Operations

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

JOIN operations combine rows from two or more tables based on related columns. Different JOIN types determine which rows are included in the result set. Essential for relational data analysis and combining data from multiple sources.

Syntax

-- INNER JOIN (default)
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- RIGHT JOIN (RIGHT OUTER JOIN)
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- FULL OUTER JOIN
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

Platform-Specific Notes

Snowflake:

• All JOIN types fully supported
• Also supports LATERAL joins for unnesting
• Efficient join optimization
• Can use USING clause for same column names

BigQuery:

• All JOIN types fully supported
• CROSS JOIN UNNEST for array expansion
• Comma joins deprecated (use explicit JOIN)
• Optimized for large-scale joins

Databricks:

• All JOIN types fully supported
• Broadcast joins for small tables
• Spark-optimized join strategies
• Supports LATERAL VIEW for array expansion

JOIN Types Visual Guide

Tables for examples:

• customers: customer_id, name
• orders: order_id, customer_id, total

Example 1: INNER JOIN (Only Matching Rows)

All Platforms:

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date;

Sample Data:

customers table:

orders table:

Result (INNER JOIN):

Note: Customer 3, 4 excluded (no orders); Order 104 excluded (customer 5 not in customers table)

Example 2: LEFT JOIN (All Left Rows + Matching Right)

All Platforms:

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total,
    COALESCE(o.total, 0) as order_total,
    CASE 
        WHEN o.order_id IS NULL THEN 'No Orders'
        ELSE 'Has Orders'
    END as order_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date;

Result (LEFT JOIN):

Note: All customers included; NULLs for customers without orders

Example 3: RIGHT JOIN (All Right Rows + Matching Left)

All Platforms:

SELECT 
    o.order_id,
    o.order_date,
    o.total,
    c.customer_id,
    c.name,
    COALESCE(c.name, 'Unknown Customer') as customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Result (RIGHT JOIN):

Note: All orders included; NULL for order 104 (customer 5 not in customers table)

Example 4: FULL OUTER JOIN (All Rows from Both Tables)

All Platforms:

SELECT 
    COALESCE(c.customer_id, o.customer_id) as customer_id,
    c.name,
    o.order_id,
    o.total,
    CASE 
        WHEN c.customer_id IS NULL THEN 'Order Without Customer'
        WHEN o.order_id IS NULL THEN 'Customer Without Orders'
        ELSE 'Matched'
    END as match_status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY customer_id, order_id;

Result (FULL OUTER JOIN):

Note: All customers AND all orders included, with NULLs where no match

Example 5: Multiple JOINs

All Platforms:

SELECT 
    c.customer_id,
    c.name as customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY c.customer_id, o.order_id, p.product_name;

Sample Data:

order_items table:

products table:

Result:

Example 6: Self JOIN

All Platforms:

-- Find employees and their managers
SELECT 
    e.employee_id,
    e.name as employee_name,
    e.manager_id,
    m.name as manager_name,
    e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.department, e.employee_id;

Sample Data (employees table):

Result:

JOIN Types Summary

Common Use Cases

• INNER JOIN: Order details with customer info
• LEFT JOIN: All customers with their orders (including those without)
• RIGHT JOIN: All products including those never ordered
• FULL OUTER JOIN: Data reconciliation, finding orphaned records
• SELF JOIN: Hierarchical data (employees/managers, categories/subcategories)

Performance Tips

1. Index join columns: Create indexes on columns used in ON clause
2. Filter early: Use WHERE before JOIN when possible
3. Join order matters: Start with smallest table
4. Avoid SELECT *: Only select needed columns
5. Use INNER JOIN when possible: More efficient than OUTER joins
6. Analyze query plans: Use EXPLAIN to optimize

Best Practices

1. Always use explicit JOIN syntax: Avoid comma joins
2. Use table aliases: Make queries more readable
3. Qualify all columns: Prefix with table alias
4. Handle NULLs: Use COALESCE for OUTER joins
5. Document complex joins: Explain business logic
6. Test with real data: Verify join conditions are correct