SQL Left Join
A left join is a join that keeps every row from the left table. A row with no match on the right still appears, with NULL in the right table columns.
Every customer, orders or not
Customers form the left table here. Hana Sato has placed no orders, so her order id comes back NULL.
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id, orders.order_id;Every product, orders or not
Products form the left table in this query. The Mouse Pad was never ordered, so its order id comes back NULL.
SELECT products.name, orders.order_id
FROM products
LEFT JOIN orders ON products.product_id = orders.product_id
ORDER BY products.product_id, orders.order_id;Finding the rows with no match
A NULL test on the right table finds the unmatched rows. Only the Mouse Pad has no order at all.
SELECT products.name
FROM products
LEFT JOIN orders ON products.product_id = orders.product_id
WHERE orders.order_id IS NULL
ORDER BY products.name;Example
SELECT customers.name, COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.name
ORDER BY order_count DESC, customers.name;Key points
- LEFT JOIN: a join that keeps every row from the table named first
- The left table is the table written before LEFT JOIN.
- Columns from an unmatched right row come back as NULL.
- Use LEFT JOIN to list every row from one side, matched or not.
- A left join keeps the whole left table and adds matches where they exist.
In this example, the left join keeps all eight customers in the result. GROUP BY gathers each customer into one row. COUNT counts only the order ids that hold a value, so Hana Sato gets a count of 0.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
In a left join, what happens to a left row with no match?