Views and Derived Tables

Derived Table

Query Inside FROM

A derived table is a subquery in the FROM clause. It lets one SELECT build rows that an outer SELECT can filter or rename.

Program

Play the script to average sales by category, then filter those derived rows.

min_avg
derived_table.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
WITH params(min_avg) AS (VALUES (11)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;

CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
WITH params(min_avg) AS (VALUES (9)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;

CREATE TABLE sales (category TEXT, amount INTEGER);
INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
WITH params(min_avg) AS (VALUES (12)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;

  1. tables ← 1 row

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);3WITH params(min_avg) AS (VALUES (11)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
    values this step4 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);3WITH params(min_avg) AS (VALUES (11)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);3WITH params(min_avg) AS (VALUES (9)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
    values this step4 rowssales

  3. result ← 2 rows

    2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);3WITH params(min_avg) AS (VALUES (9)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (category TEXT, amount INTEGER);2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);3WITH params(min_avg) AS (VALUES (12)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
    values this step4 rowssales

  3. result ← 1 row

    2INSERT INTO sales VALUES ('book', 10), ('book', 14), ('tool', 8), ('tool', 12);3WITH params(min_avg) AS (VALUES (12)) SELECT category, avg_amount FROM (SELECT category, AVG(amount) AS avg_amount FROM sales GROUP BY category) AS category_avg WHERE avg_amount >= (SELECT min_avg FROM params) ORDER BY category;
    values this step1 rowresult

Follow the Derived Rows

  1. sales starts with book 10, book 14, tool 8, and tool 12.
  2. The inner query averages each category.
  3. The derived rows are book 12.0 and tool 10.0.
  4. The default min_avg is 11.
  5. Only book 12.0 passes, so the output row is book | 12.0. | category | source amounts | average | kept at 11? | | --- | --- | --- | --- | | book | 10, 14 | 12.0 | yes | | tool | 8, 12 | 10.0 | no |
derived table `FROM (SELECT ...) AS category_avg` turns a query result into an input table.
outer filter The outer `WHERE` filters rows produced by the inner query.
aggregate first The inner query groups and averages before the outer query filters.

Exercise: derived_table.sql

Reproduce the default row book | 12.0, then use the pinned min_avg variants 9 and 12 to predict book | 12.0 plus tool | 10.0, and then book | 12.0 only.