Joins

Inner Join

Matching Rows

An inner join combines rows when keys match in both tables.

Program

Play the query to connect customers to their orders.

inner_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');
INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);
SELECT customers.name, orders.id, orders.total
FROM customers
JOIN orders ON customers.id = orders.customer_id
ORDER BY orders.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');
    values this step0 rowsorders

  3. customers ← 2 rows

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

  4. orders ← 2 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);5SELECT customers.name, orders.id, orders.total
    values this step2 rowsorders

  5. result ← 2 rows

    4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12);5SELECT customers.name, orders.id, orders.total6FROM customers7JOIN orders ON customers.id = orders.customer_id8ORDER BY orders.id;
    values this step2 rowsresult

Follow the Match

  1. Start with one row from customers.
  2. Look for rows in orders with the same customer id.
  3. Keep the pair only when the ids match.
  4. Rows without a match do not appear in an inner join. | customers.id | orders.customer_id | Result | | --- | --- | --- | | 1 | 1 | joined row appears | | 2 | no matching order | no joined row |
JOIN `JOIN` combines tables.
ON `ON customers.id = orders.customer_id` is the matching rule.
qualified name `orders.id` chooses the `id` column from `orders`.

Exercise: inner_join.sql

Join customers to orders where ids match and return only matched customer-order rows