Intermediate · Lesson 7 of 14

JOINs

Combine tables on a key, and watch out for grain.

The relational core

JOIN brings columns from a second table into the result, matched on a key such as customer_id. The keyword determines which rows survive.

INNER, LEFT, and the anti-join

INNER JOIN keeps only matching pairs. LEFT JOIN keeps every row from the left table even without a match, using NULLs on the right. A LEFT JOIN with a WHERE test for NULL is an anti-join: "who never did X".

Grain is the gotcha

A one-to-many join multiplies rows. Before writing any join, say the intended grain: one row per order, per user, per day. Interviewers grade that sentence.

Examples

INNER JOIN

Every order with the matching customer name.

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

LEFT JOIN with no match

All customers; NULL order_id when they have none.

SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;

Three table join

Units sold per product through the join chain.

SELECT p.name, SUM(i.quantity) AS units FROM products p JOIN order_items i ON i.product_id = p.product_id JOIN orders o ON o.order_id = i.order_id WHERE o.status = 'completed' GROUP BY p.name ORDER BY units DESC;