Outer joins include all rows from one table even when there's no match in the other table. Understanding LEFT and RIGHT joins is essential for complete data analysis.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table plus matching rows from the right table. Non-matching rows show NULL for right table columns.

-- Get ALL customers, including those without orders
SELECT
    customers.name,
    customers.email,
    orders.order_id,
    orders.total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Result includes every customer. Customers without orders will have NULL in the order_id and total columns.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table plus matching rows from the left table. Less commonly used than LEFT JOIN.

-- Get ALL orders, including orphaned orders without customer info
SELECT
    customers.name,
    orders.order_id,
    orders.total
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

LEFT JOIN vs RIGHT JOIN

These queries are equivalent - just with tables swapped:

-- Using LEFT JOIN
SELECT * FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Same result using RIGHT JOIN (just swap the tables)
SELECT * FROM orders o
RIGHT JOIN customers c ON c.customer_id = o.customer_id;

Best Practice: Most developers prefer LEFT JOIN because it's more intuitive to read from left to right.

Finding Non-Matching Records

Use LEFT JOIN with a NULL check to find records that don't have matches:

Customers with No Orders

SELECT
    customers.customer_id,
    customers.name,
    customers.email,
    customers.signup_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

Products Never Sold

SELECT
    products.product_id,
    products.product_name,
    products.price
FROM products
LEFT JOIN order_items ON products.product_id = order_items.product_id
WHERE order_items.order_id IS NULL;

Practical Use Cases

Customer Engagement Report

SELECT
    c.customer_id,
    c.name,
    c.signup_date,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.total), 0) as lifetime_value,
    CASE
        WHEN COUNT(o.order_id) = 0 THEN 'Never Purchased'
        WHEN COUNT(o.order_id) < 3 THEN 'Low Engagement'
        ELSE 'Active Customer'
    END as customer_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.signup_date
ORDER BY order_count DESC;

Email List with Purchase History

SELECT
    u.email,
    u.first_name,
    u.last_name,
    MAX(o.order_date) as last_purchase_date,
    DATEDIFF(CURDATE(), MAX(o.order_date)) as days_since_purchase
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.email_opt_in = true
GROUP BY u.email, u.first_name, u.last_name;

Complete Inventory Report

SELECT
    p.product_id,
    p.product_name,
    p.stock_quantity,
    COALESCE(SUM(oi.quantity), 0) as total_sold,
    COALESCE(SUM(oi.quantity * oi.price), 0) as revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.stock_quantity
ORDER BY revenue DESC;