Joins

Left Join

Keeping Unmatched Rows

A left join keeps every row from the left table, even when the right side has no match.

Program

Play the query to keep a customer with no order.

left_join.sql 
CREATE TABLE customers (id INTEGER, name TEXT);
CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);
SELECT customers.name, orders.total FROM customers LEFT JOIN orders ON customers.id = orders.customer_id ORDER BY customers.id;

Follow the Kept Row

  1. Start with every row from the left table, customers.
  2. Add order data when a matching order exists.
  3. Keep the customer even when no order matches.
  4. Missing order columns show up as NULL. | Left row | Right match | Result | | --- | --- | --- | | customer with an order | found | customer plus order columns | | customer with no order | missing | customer plus NULL order columns |
LEFT JOIN `LEFT JOIN` preserves the left table's rows.
NULL Missing right-side values appear as `NULL`.
outer join Left joins are a common outer-join form.

Exercise: left_join.sql

List every customer and show order info when it exists, using NULL for customers without orders