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.

join_filter.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), (103, 1, 8);
WITH params(min_total) AS (VALUES ()) 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 ()) 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 ()) 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;

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