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 
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;

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