Joins

Join Then Filter

High-Value Matches

A joined result can still be filtered. The WHERE condition can reference either table.

Program

Play the query to keep only high-value customer orders.

min_total
join_filter.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), (103, 1, 8);
WITH params(min_total) AS (VALUES (20)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;

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), (103, 1, 8);
WITH params(min_total) AS (VALUES (10)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;

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), (103, 1, 8);
WITH params(min_total) AS (VALUES (31)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;

  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), (103, 1, 8);
    values this step2 rowscustomers

  4. orders ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12), (103, 1, 8);5WITH params(min_total) AS (VALUES (20)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;
    values this step3 rowsorders

  5. result ← 1 row

    4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12), (103, 1, 8);5WITH params(min_total) AS (VALUES (20)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;
    values this step1 rowresult

  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), (103, 1, 8);
    values this step2 rowscustomers

  4. orders ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12), (103, 1, 8);5WITH params(min_total) AS (VALUES (10)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;
    values this step3 rowsorders

  5. result ← 2 rows

    4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12), (103, 1, 8);5WITH params(min_total) AS (VALUES (10)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;
    values this step2 rowsresult

  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), (103, 1, 8);
    values this step2 rowscustomers

  4. orders ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12), (103, 1, 8);5WITH params(min_total) AS (VALUES (31)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;
    values this step3 rowsorders

  5. result ← 0 rows

    4INSERT INTO orders VALUES (101, 1, 30), (102, 2, 12), (103, 1, 8);5WITH params(min_total) AS (VALUES (31)) SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.total >= (SELECT min_total FROM params) ORDER BY orders.total DESC;
    values this step0 rowsresult

Join, Then Filter

  1. First, join customers to matching orders.
  2. Next, check each joined row's order total.
  3. Keep only rows where the total passes the WHERE rule.
  4. Sort the kept rows so the highest totals appear first. | Joined row total | Filter result | | --- | --- | | 15 | removed | | 31 | kept |
join filter `WHERE orders.total >= 20` filters the joined rows.
DESC `ORDER BY ... DESC` sorts high values first.
composition Joins, filters, and sorting compose in one query.

Exercise: join_filter.sql

Join customers to orders, keep only totals at least 20, and sort highest totals first