MySQL Joins

-- INNER JOIN: only rows with a match in BOTH tables
-- Returns customers who have placed at least one order
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.order_id,
    o.total,
    o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- LEFT JOIN: all rows from the LEFT table, matched rows from the right
-- Returns ALL customers, including those with no orders (NULL for order columns)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    COUNT(o.order_id)                       AS order_count,
    COALESCE(SUM(o.total), 0)               AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- RIGHT JOIN: all rows from the RIGHT table, matched rows from the left
-- Returns all orders, including those with no matching customer (unusual but possible)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.order_id,
    o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- FULL OUTER JOIN: MySQL doesn't support FULL OUTER JOIN directly
-- Simulate it with LEFT JOIN UNION RIGHT JOIN
SELECT c.customer_id, c.first_name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.customer_id, c.first_name, o.order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- CROSS JOIN: every row from table A combined with every row from table B
-- (Cartesian product — use carefully, can produce huge result sets)
SELECT c.first_name, p.name AS product_name
FROM customers c
CROSS JOIN products p
LIMIT 20;

-- SELF JOIN: join a table to itself
-- Example: find customers who share the same last name
SELECT
    a.customer_id AS id1,
    a.first_name  AS name1,
    b.customer_id AS id2,
    b.first_name  AS name2,
    a.last_name
FROM customers a
INNER JOIN customers b
    ON a.last_name = b.last_name
    AND a.customer_id < b.customer_id
ORDER BY a.last_name;

-- Full order detail: customer + order + items + product names
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.order_id,
    o.status,
    o.ordered_at,
    p.name                                 AS 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.status IN ('shipped', 'delivered')
ORDER BY o.ordered_at DESC, o.order_id, oi.item_id;

-- Find customers who have never placed an order (LEFT JOIN + NULL check)
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Filtering in ON vs WHERE behaves differently with LEFT JOIN

-- Filter in WHERE: excludes non-matching rows (effectively becomes INNER JOIN)
SELECT c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';   -- customers with no orders are excluded

-- Filter in ON: keeps all left-table rows, filters only the joined rows
SELECT c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
    AND o.status = 'delivered';  -- customers with no delivered orders still appear (NULL)