Views and Query Reuse

In the SQL for Beginners series you have written the same shape of query many times: the customer list with the full name built out of two columns, the totals per country, the join that pairs orders with order_items. Chapter 20 of this 24-chapter course introduces views — a SELECT you write once and store under a name, so every later query can read from it as if it were a table.

A stored query with a name

A view is not a copy of data. It is a definition: the SQL text of a query, saved in the schema, that SQLite re-runs every time someone selects from it. Start with the query you keep retyping — two name columns folded into one, only the columns a report needs:

SELECT id,
       first_name || ' ' || last_name AS name,
       city,
       country
FROM customers
ORDER BY id;

Nothing is wrong with it, and nothing about it is memorable. Give it a name instead:

CREATE VIEW customer_directory AS
SELECT id,
       first_name || ' ' || last_name AS name,
       city,
       country
FROM customers;

From that moment customer_directory behaves like a table in the FROM clause, and you can filter, sort and join it with everything you learned in chapter 3:

sqlite3 shop.db "SELECT * FROM customer_directory WHERE country = 'India' ORDER BY id;"

Expected output:

1|Aarav Sharma|Mumbai|India
2|Priya Nair|Delhi|India

Notice what did not happen: no rows were copied anywhere. Change a customer’s city in customers tomorrow and the view will show the new value the moment you query it. That is the whole trick — the view is a name for the query, not a snapshot of the answer.

A view is also the simplest access control you have: expose exactly the columns a reader is allowed to see. customer_directory never mentions email, so asking for it fails at prepare time:

sqlite3 shop.db "SELECT email FROM customer_directory;"

Expected output:

Error: in prepare, no such column: email
  SELECT email FROM customer_directory;
         ^--- error here

Views over joins and aggregates

The queries worth naming are the ones you are tired of retyping: aggregates, joins, and anything with a join and an aggregate in it. Two views cover most reporting needs:

CREATE VIEW country_counts AS
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country;

CREATE VIEW customer_orders AS
SELECT c.id,
       c.first_name || ' ' || c.last_name AS name,
       COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Now the aggregate is a two-word sentence, and — just like a table — you may sort its output any way you like without rewriting the group by:

sqlite3 shop.db "SELECT country, customers FROM country_counts ORDER BY customers DESC, country;"

Expected output:

India|2
Canada|1
Germany|1
Kenya|1
Singapore|1

Because a view is a relation, narrowing it is just another WHERE — the GROUP BY stays where you left it:

sqlite3 shop.db "SELECT country, customers FROM country_counts WHERE customers > 1;"

Expected output: India|2

The join view has one row per customer whether they ordered or not, so a quick COUNT(*) over it confirms it still spans the whole table:

sqlite3 shop.db "SELECT COUNT(*) FROM customer_orders;"

Expected output: 6

One limitation to hold on to: a view takes no arguments. The WHERE inside it is fixed at creation time, so you narrow the result in your own query (SELECT * FROM country_counts WHERE country = ...) — real parameters belong to the driver placeholders you will meet in chapter 23.

How a view is stored

Views live in the same catalogue as tables, which means you can always find out what a database is hiding behind a name:

sqlite3 shop.db "SELECT name FROM sqlite_master WHERE type = 'view' ORDER BY name;"

Expected output:

country_counts
customer_directory
customer_orders

The definition itself is one column away, and the .schema dot-command shows it the same way it shows tables:

sqlite3 shop.db ".schema customer_directory"

Expected output:

CREATE VIEW customer_directory AS
SELECT id,
       first_name || ' ' || last_name AS name,
       city,
       country
FROM customers
/* customer_directory(id,name,city,country) */;

That is all that exists — one statement plus a trailing comment that records the view’s column names, the only place the result’s shape is written down. Because the text is stored rather than the result, a view depends on its base table’s columns: rename city and customer_directory stops preparing until you fix it too. And not every view has to be permanent — prefix the statement with TEMP and the view lives in the session’s private schema, handy for a throwaway join in the CLI:

sqlite3 shop.db "CREATE TEMP VIEW multi_customer_country AS
SELECT country, customers FROM country_counts WHERE customers > 1;
SELECT * FROM multi_customer_country;"

Expected output: India|2

Reconnect and it has vanished: SELECT name FROM sqlite_temp_master WHERE type = 'view'; comes back empty in a fresh session, while sqlite_master still holds every permanent definition.

Views are read-only in SQLite

It is tempting to treat a view as a window you can push changes through. SQLite does not allow it:

sqlite3 shop.db "UPDATE customer_directory SET city = 'Pune' WHERE id = 1;"

Expected output:

Error: in prepare, cannot modify customer_directory because it is a view

Views exist to be read from; writes go to the base tables, where constraints and triggers still guard them. When a view really is your application’s write surface, SQLite’s answer is an INSTEAD OF trigger — CREATE TRIGGER ... INSTEAD OF UPDATE ON customer_directory ... reroutes the statement to whatever logic you write. Other engines differ: PostgreSQL and MySQL happily allow INSERT, UPDATE and DELETE through a simple view (one table, no aggregates), and both support WITH CHECK OPTION, which rejects any write that would fall outside the view’s own WHERE — SQLite has no such clause, so that guard belongs in application code or a trigger.

Composing and retiring views

Views compose: a view built on a view is still just a saved query, and the layering can mirror how you explain the data to someone else:

CREATE VIEW IF NOT EXISTS india_directory AS
SELECT * FROM customer_directory WHERE country = 'India';

sqlite3 shop.db "SELECT * FROM india_directory ORDER BY id;"

Expected output:

1|Aarav Sharma|Mumbai|India
2|Priya Nair|Delhi|India

Equally important is removing one you no longer want, and re-listing to prove it is gone:

sqlite3 shop.db "DROP VIEW IF EXISTS country_counts;
SELECT name FROM sqlite_master WHERE type = 'view' ORDER BY name;"

Expected output:

customer_directory
customer_orders
india_directory

Two habits make view definitions deployable. CREATE VIEW IF NOT EXISTS keeps a setup script re-runnable without tripping over yesterday’s run, and DROP VIEW IF EXISTS ... immediately before a fresh CREATE is how you change a definition in place — SQLite has no CREATE OR REPLACE VIEW, while PostgreSQL and MySQL both do, so the script that works everywhere drops first.

When the view is too slow to recompute

Because every read re-runs the inner query, a view over a wide join can hide an expensive report behind a friendly name — the cost returns on every call, and no index from chapter 19 changes that arithmetic. When the answer only needs refreshing occasionally, store it instead: PostgreSQL offers CREATE MATERIALIZED VIEW monthly_totals AS SELECT ...; plus REFRESH MATERIALIZED VIEW, SQL Server indexes an ordinary view behind the scenes, and MySQL has no materialized views at all, so people keep a summary table and refill it. In SQLite the portable pattern is the same summary table — populate it inside a transaction so readers never see a half-written total:

CREATE TABLE country_totals (
    country TEXT PRIMARY KEY,
    customers INTEGER NOT NULL
);
-- refill on a schedule: BEGIN; DELETE FROM country_totals; INSERT ... ; COMMIT;

The decision rule is simple: if the view answers in milliseconds, keep it a view, because a view can never go stale. If aggregating the rows is slow enough to notice and the totals only change when the data changes, a stored snapshot nobody can tell from a fresh one is the better trade.

Common mistakes

• Treating a view as a copy — nothing is snapshotted, so edits to the base tables show up immediately, and an expensive inner query runs on every read. Store results when you need a snapshot.
• Trying to write through it — SQLite rejects UPDATE on any view; write to the base table, or add an INSTEAD OF trigger when the view is the real interface.
• Defining views with SELECT * — the day a column is added, reordered or renamed underneath, every consumer shifts with it. Name the columns the way customer_directory does.
• Chaining views ten deep — a one-line query whose meaning lives in five definitions is harder to debug than the SQL it replaced. Keep layers shallow and read them with .schema.
• Forgetting the view exists — two teams then write two versions of the same logic. List sqlite_master before you invent a new reporting query.

Practice checklist

1. Create customer_directory and query it three ways: filter, sort and limit.
2. Build a view over a join you wrote by hand in chapter 12 and compare the two statements.
3. Sort country_counts by name instead of count and confirm the view itself never changed.
4. Attempt an UPDATE on a view and save the exact error SQLite gives you.
5. Add india_directory on top of customer_directory, then drop it and prove the listing is back to the original three.

What's next?

Views give a single reader one consistent shape of the data. Next you handle what happens when several statements must succeed or fail together: Transactions and Concurrency covers ACID, BEGIN, COMMIT, ROLLBACK and what goes wrong when two connections write at once.