LEFT, RIGHT & FULL OUTER JOIN

INNER JOIN drops anything without a match. But a lot of the time you want to keep those rows. "Show all customers and their orders, including people who never ordered", that's a LEFT JOIN.

LEFT JOIN

Keep every row from the left table (the one in FROM). If there's a match on the right, great. If not, the right side columns come back as NULL.

SELECT c.first_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

first_name	order_id	amount
Aman	101	1098
...	...	...
Karan	NULL	NULL
...	...	...
Arjun	NULL	NULL

Karan and Arjun are still here, just with NULLs. With INNER JOIN they'd be gone.

counting with a LEFT JOIN (careful)

Orders per customer, including zero:

SELECT c.first_name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name;

Use COUNT(o.order_id) not COUNT(*). COUNT(*) counts the row, and Karan does have a row (full of NULLs), so he'd show 1 order. COUNT(o.order_id) skips NULLs so he correctly shows 0.

Same for SUM, it'd give NULL for Karan, wrap it in COALESCE:

COALESCE(SUM(o.amount), 0) AS total_spent

finding things with no match

Super common pattern. Customers who never ordered:

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

LEFT JOIN keeps everyone, then WHERE keeps only the ones where nothing matched. There's a whole topic on this (anti-joins).

the trap: WHERE on the right table kills your LEFT JOIN

-- "all customers, with their delivered orders"
SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';

Karan and Arjun disappear again. Why? Their o.status is NULL, NULL = 'delivered' isn't true, so WHERE throws them out. You basically turned it back into an INNER JOIN.

Fix, put the condition in ON:

SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'delivered';

Now it means "match only delivered orders, but keep every customer either way". This one's important enough that it has its own topic (join predicates).

RIGHT JOIN

Same thing but keeps everything from the right table. A RIGHT JOIN B is exactly B LEFT JOIN A. Most people just flip the tables and use LEFT JOIN always, it's easier to read. You'll rarely write RIGHT JOIN.

FULL OUTER JOIN

Keeps everything from both sides, NULLs wherever there's no match. Useful for comparing two lists and seeing what's missing on either side.

SELECT c.customer_id AS in_customers, a.customer_id AS in_addresses
FROM customers c
FULL OUTER JOIN (SELECT DISTINCT customer_id FROM addresses) a
  ON c.customer_id = a.customer_id;

Mysql doesn't have FULL OUTER JOIN, you fake it with a LEFT JOIN UNION a RIGHT JOIN.

CROSS JOIN

Every row paired with every row, no ON. 8 customers × 8 products = 64 rows. Sounds useless but it's handy for generating combos, like every customer × every month for a report.

SELECT c.first_name, p.product_name
FROM customers c
CROSS JOIN products p;

stuff that trips people up

• WHERE on the right table turning LEFT into INNER.
• COUNT(*) giving 1 for people with no matches.
• forgetting COALESCE on sums.
• left vs right, just always use LEFT and put the "keep everything" table first.

practice

Check queries.sql.

-- LEFT / RIGHT / FULL joins
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT c.first_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

SELECT c.first_name, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name
ORDER BY c.customer_id;

-- trap: becomes an inner join
SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';

-- fixed
SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'delivered';


-- practice ---------------------------------------------------------

-- 1. every product and how many times it was ordered, including products never ordered (show 0)
-- 2. products that have never been ordered
-- 3. every customer and the number of addresses they have, 0 if none
-- 4. every customer and their total delivered spend, 0 if nothing delivered
-- 5. every employee and their manager's name ('no manager' for the top boss)
--    hint: you're joining employees to itself, there's a topic on that next


-- answers ----------------------------------------------------------

-- 1
SELECT p.product_name, COUNT(oi.order_id) AS times_ordered
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY times_ordered DESC;

-- 2
SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;
-- empty! every product has been ordered at least once. not every query has an interesting answer lol

-- 3
SELECT c.first_name, COUNT(a.address_id) AS addresses
FROM customers c
LEFT JOIN addresses a ON c.customer_id = a.customer_id
GROUP BY c.customer_id, c.first_name;

-- 4
SELECT c.first_name, COALESCE(SUM(o.amount), 0) AS delivered_spend
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id, c.first_name
ORDER BY delivered_spend DESC;

-- 5
SELECT e.name, COALESCE(m.name, 'no manager') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;