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
Replay: real traced execution (multi-file project)
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;

  1. customers ← 0 rows

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);
    values this step0 rowscustomers

  2. orders ← 0 rows

    1CREATE TABLE customers (id INTEGER, name TEXT);2CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
    values this step0 rowsorders

  3. customers ← 3 rows

    2CREATE TABLE orders (id INTEGER, customer_id INTEGER, total INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);
    values this step3 rowscustomers

  4. orders ← 2 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);5SELECT customers.name, orders.total FROM customers LEFT JOIN orders ON customers.id = orders.customer_id ORDER BY customers.id;
    values this step2 rowsorders

  5. result ← 3 rows

    4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);5SELECT customers.name, orders.total FROM customers LEFT JOIN orders ON customers.id = orders.customer_id ORDER BY customers.id;
    values this step3 rowsresult

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