Views and Derived Tables

Create View

Saving a Query Shape

A view stores a named SELECT statement. Later queries can read the view like a table while the data still comes from the base table.

Program

Play the script to create an order summary view and filter it with a selectable minimum total.

min_total
create_view.sql
Replay: real traced execution (multi-file project)
CREATE TABLE orders (region TEXT, amount INTEGER);
INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
WITH params(min_total) AS (VALUES (20)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;

CREATE TABLE orders (region TEXT, amount INTEGER);
INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
WITH params(min_total) AS (VALUES (15)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;

CREATE TABLE orders (region TEXT, amount INTEGER);
INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
WITH params(min_total) AS (VALUES (22)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;

  1. tables ← 1 row

    1CREATE TABLE orders (region TEXT, amount INTEGER);2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
    values this step1 rowtables

  2. orders ← 3 rows

    1CREATE TABLE orders (region TEXT, amount INTEGER);2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
    values this step3 rowsorders

  3. views ← 1 row

    2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;4WITH params(min_total) AS (VALUES (20)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;
    values this step1 rowviews

  4. result ← 1 row

    3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;4WITH params(min_total) AS (VALUES (20)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE orders (region TEXT, amount INTEGER);2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
    values this step1 rowtables

  2. orders ← 3 rows

    1CREATE TABLE orders (region TEXT, amount INTEGER);2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
    values this step3 rowsorders

  3. views ← 1 row

    2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;4WITH params(min_total) AS (VALUES (15)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;
    values this step1 rowviews

  4. result ← 2 rows

    3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;4WITH params(min_total) AS (VALUES (15)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE orders (region TEXT, amount INTEGER);2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
    values this step1 rowtables

  2. orders ← 3 rows

    1CREATE TABLE orders (region TEXT, amount INTEGER);2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
    values this step3 rowsorders

  3. views ← 1 row

    2INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;4WITH params(min_total) AS (VALUES (22)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;
    values this step1 rowviews

  4. result ← 0 rows

    3CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;4WITH params(min_total) AS (VALUES (22)) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;
    values this step0 rowsresult

Follow the View Rows

  1. orders starts with east 12, east 9, and west 18.
  2. The region_totals view groups orders by region.
  3. The view rows are east 21 and west 18.
  4. The default min_total is 20.
  5. Only east 21 passes, so the output row is east | 21. | region | source amounts | view total | kept at 20? | | --- | --- | --- | --- | | east | 12, 9 | 21 | yes | | west | 18 | 18 | no |
CREATE VIEW `CREATE VIEW name AS SELECT ...` gives a query a reusable name.
view query Reading from `region_totals` evaluates the saved SELECT over current table data.
parameter CTE `params` holds the selectable minimum total used by the filter.

Exercise: create_view.sql

Reproduce the default row east | 21, then use the pinned min_total variants 15 and 22 to predict east | 21 plus west | 18, and then no rows.