SQL / DEFINING TABLES AND CONSTRAINTS
Views for hiding complexity behind a name
Define a view so a long join-and-aggregate query becomes a name you can select from, and know why it never needs refreshing.
What you will learn
- Wrap a finished SELECT in CREATE VIEW and query the result like a table
- Filter a view's aggregate column with plain WHERE instead of repeating HAVING
- Explain why views never go stale: they store query text, not rows
- Use WITH CHECK OPTION so rows written through a view stay visible in it
Understanding Views for hiding complexity behind a name
A view is a named SELECT stored in the schema next to your tables. Nothing is copied: CREATE VIEW records the statement text, and each time you name the view the engine substitutes that statement into your query and plans the whole thing as one unit. That substitution is why a view can never be out of date, and why your outer conditions are folded into the same plan rather than applied to a finished result, so a filter like country = 'NG' on the view reaches the customer scan instead of running after every balance has been computed.
The useful mental model is vocabulary, not cache. customer_balance does not store balances; it gives one name to the join condition, the paid = 0 rule, and the SUM, so five different reports cannot each get the rule slightly wrong. Because the view's select list becomes its column list, every expression needs an AS alias: SUM(i.amount) AS owed turns an aggregate into an ordinary column named owed, which is why the outer query filters it with WHERE and never needs HAVING again.
Views also matter when you decide what may be written. A view over a single table with no GROUP BY, DISTINCT, or aggregate is auto-updatable in PostgreSQL and MySQL, so INSERT and UPDATE are rewritten against the base table; add WITH CHECK OPTION and the view's WHERE becomes a constraint on those writes, rejecting rows the view could not show. A view over a join or a SUM is read-only instead, because one value in it corresponds to no single base row, so there is nothing for the engine to update.
CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT NOT NULL
);
CREATE TABLE invoice (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customer(id),
amount INTEGER NOT NULL,
paid INTEGER NOT NULL DEFAULT 0
);
INSERT INTO customer VALUES (1, 'Halden', 'NO'), (2, 'Ferrar', 'IT'), (3, 'Okoye', 'NG');
INSERT INTO invoice VALUES (1, 1, 120, 1), (2, 1, 80, 0), (3, 2, 46, 0), (4, 2, 46, 1), (5, 3, 300, 0);
CREATE VIEW customer_balance AS
SELECT c.id AS customer_id,
c.name AS name,
c.country AS country,
SUM(i.amount) AS owed
FROM customer c
JOIN invoice i ON i.customer_id = c.id
WHERE i.paid = 0
GROUP BY c.id, c.name, c.country;
SELECT name, owed
FROM customer_balance
WHERE owed > 50
ORDER BY owed DESC;A view is a name bound to a SELECT that is expanded into your query at run time, so it stores no rows and always reflects the current base tables.
Worked examples
A view holds no rows
Shows that a change to the base table appears through the view with no refresh step.
CREATE TABLE ticket (id INTEGER PRIMARY KEY, status TEXT NOT NULL);
INSERT INTO ticket VALUES (1, 'open'), (2, 'closed'), (3, 'open');
CREATE VIEW open_ticket AS
SELECT id FROM ticket WHERE status = 'open';
SELECT COUNT(*) AS open_now FROM open_ticket;
INSERT INTO ticket VALUES (4, 'open');
SELECT COUNT(*) AS open_now FROM open_ticket;Example explained
Line 1CREATE VIEW stores the text SELECT id FROM ticket WHERE status = 'open'; no ids are saved anywhere.
Line 2The first COUNT(*) executes that stored SELECT and finds ids 1 and 3.
Line 3The INSERT touches only the base table, yet the second COUNT(*) returns 3 because the definition is executed again.
Line 4There is no refresh command for a plain view, and none is needed by construction.
WITH CHECK OPTION as a constraint on writes
Demonstrates PostgreSQL rejecting an insert through a view that the view itself could not display.
CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL);
CREATE VIEW norwegian_customer AS
SELECT id, name, country FROM customer WHERE country = 'NO'
WITH CHECK OPTION;
INSERT INTO norwegian_customer VALUES (1, 'Halden', 'NO');
INSERT INTO norwegian_customer VALUES (2, 'Sato', 'JP');Example explained
Line 1The view reads one table with no aggregate or DISTINCT, so PostgreSQL treats it as insertable and rewrites the INSERT against customer.
Line 2INSERT 0 1 confirms the first row was written to the base table through the view.
Line 3WITH CHECK OPTION re-tests each new row against WHERE country = 'NO', so 'JP' is refused.
Line 4Without that clause the second INSERT would succeed and the row would be immediately invisible through the view that created it.
Layering one view on another
Builds a query in two named stages, where the second view selects from the first.
CREATE TABLE reading (sensor TEXT NOT NULL, celsius INTEGER NOT NULL);
INSERT INTO reading VALUES ('a', 21), ('a', 35), ('b', 19), ('b', 41), ('a', 30);
CREATE VIEW hot_reading AS
SELECT sensor, celsius FROM reading WHERE celsius >= 30;
CREATE VIEW hot_per_sensor AS
SELECT sensor, COUNT(*) AS hits FROM hot_reading GROUP BY sensor;
SELECT sensor, hits FROM hot_per_sensor ORDER BY sensor;Example explained
Line 1hot_reading gives the 30 degree threshold a single name, so the meaning of hot is defined in one place.
Line 2hot_per_sensor selects FROM hot_reading exactly as it would from a table; nesting is not limited to one level.
Line 3Both definitions are inlined, so what runs is one scan of reading with grouping, not two stored intermediate results.
Line 4hot_per_sensor now depends on hot_reading: PostgreSQL refuses to drop hot_reading without CASCADE, while SQLite drops it and the dependent view fails at its next query.
Important notes
A plain view has no storage and cannot be indexed; when you genuinely need stored results, that is a materialized view in PostgreSQL or a real table, and both go stale until refreshed.
ORDER BY inside a view definition is not a guarantee, since the outer query is free to reorder rows; put ORDER BY in the query that reads the view.
Common mistakes
Treating a view as a cache: the definition is re-executed on every reference, so a nine second join wrapped in a view still costs nine seconds per query.
Writing SELECT * in the definition: PostgreSQL and MySQL expand the star at CREATE time, so a column added later never appears through the view, and dropping a starred column leaves the view broken.
Trying to UPDATE a view built on a join or GROUP BY: the write is rejected because a summed or joined value maps to no single base row, and the fix is to write to the base table instead.
Try it yourself
Change, predict, then run
Create product and sale tables, then define a view product_revenue that joins them and sums quantity * unit_price per product, and query it with WHERE revenue > 100. Insert one more sale row and re-run the identical query to see the total change without editing the view.
Open the SQL workspaceCheck your understanding
A view open_balance sums unpaid invoices per customer. You run SELECT * FROM open_balance, someone inserts another unpaid invoice, and you run the identical statement again. What do you see the second time?
- The same rows as the first run, because CREATE VIEW stored a snapshot of the result
- The new invoice only after you run a REFRESH command on the view
- The new invoice included, because referencing the view re-executes its SELECT against the current base tables
- An error, because the stored view result no longer matches the base tables
Show answer
CREATE VIEW saves the query text, so naming the view runs that query again on whatever the tables now contain, and the second total includes the new invoice. Option 2 is tempting because refreshing is exactly how a materialized view works, but a materialized view is a different object that does store rows; a plain view has nothing to refresh.