Saved queries with views

What is a view?

A view is a SELECT query saved in the database under a name. Once it exists, you can write SELECT * FROM overdue_loans and SQLite runs the saved query for you, as if overdue_loans were a table. The view itself holds no rows. It holds only the query text.

You make one with CREATE VIEW name AS followed by any SELECT: joins, WHERE, GROUP BY, window functions, WITH steps. The columns of the view are the columns of that SELECT, with the names they have there. If two joined tables both have a name column, give each an alias inside the view, or the view gets two columns with clashing names.

Run the example, then open the Tables view. The library's three tables are unchanged and no new table appeared: a view is a stored definition, not a stored result. Running SELECT name, sql FROM sqlite_master WHERE type = 'view' shows the saved text.

CREATE VIEW loan_details AS
SELECT l.id, b.title, b.author, m.name AS member_name,
  l.loaned_on, l.due_on, l.returned_on
FROM loans AS l
JOIN books AS b ON b.id = l.book_id
JOIN members AS m ON m.id = l.member_id;

SELECT title, member_name, due_on
FROM loan_details
WHERE returned_on IS NULL
ORDER BY due_on;

Why would I save a query as a view?

Reuse is the first reason. The join between loans, books and members appears in almost every library report. Written once as a view, every later query starts from a readable table of loan details instead of repeating three tables and two ON conditions.

Consistent definitions are the second. 'Overdue' sounds obvious until two people write it differently: one uses due_on < today, another due_on <= today, a third forgets to exclude returned books. If everyone queries overdue_loans, there is one definition, and fixing it fixes every report at once.

Hiding complexity is the third. A colleague who only needs the overdue list doesn't need to know about the join or the NULL test. In larger databases, a view can also expose only some columns, so a report reads names and due dates without seeing members' addresses. That is a useful tidy-up in SQLite. In PostgreSQL and MySQL, which have user accounts and permissions, it doubles as a security tool.

Does a view copy the data?

No. Every time you read a view, SQLite runs its saved query against the tables as they are at that moment. Change a table and the view's answer changes too, with nothing to refresh.

The example proves it. It creates the overdue view, marks The Overstory as returned with an UPDATE on the loans table, then reads the view again. The Overstory has gone from the list. Open the Changes view to see the single changed cell in loans, and notice that the view never appears there, because it has no rows of its own to change.

PostgreSQL also has materialized views, which do store a copy of the result and must be refreshed with REFRESH MATERIALIZED VIEW. SQLite and MySQL don't have them. If you need a frozen snapshot in SQLite, create a real table with CREATE TABLE snapshot AS SELECT …, and remember it won't update itself.

CREATE VIEW overdue AS
SELECT b.title, l.due_on
FROM loans AS l
JOIN books AS b ON b.id = l.book_id
WHERE l.returned_on IS NULL
  AND l.due_on < '2026-10-01';

UPDATE loans
SET returned_on = '2026-10-01'
WHERE book_id = 4 AND returned_on IS NULL;

SELECT * FROM overdue;

Can I change data through a view?

Not in SQLite. Views here are read-only: INSERT INTO overdue_loans …, UPDATE overdue_loans … and DELETE FROM overdue_loans … all fail with 'cannot modify overdue_loans because it is a view'. Change the underlying table instead, and the view will show the change.

PostgreSQL and MySQL allow updates through simple views, ones built on a single table without grouping or DISTINCT. SQLite can imitate this with an INSTEAD OF trigger, which tells it what to do to the real tables when someone writes to the view. That is an advanced tool; for most work, writing to the table directly is simpler.

How do I change or remove a view?

DROP VIEW overdue_loans; removes the view. Only the saved query disappears. The tables and their rows are untouched, which makes dropping a view a low-risk operation, unlike dropping a table.

SQLite has no ALTER VIEW and no CREATE OR REPLACE VIEW (PostgreSQL and MySQL have the second). To change a view in SQLite, drop it and create it again with the new SELECT. DROP VIEW IF EXISTS avoids an error when the view isn't there, and CREATE VIEW IF NOT EXISTS skips creation when the name is taken. Views and tables share the same names, so you can't have a table and a view both called loans.

CREATE VIEW books_out AS
SELECT book_id FROM loans WHERE returned_on IS NULL;

DROP VIEW IF EXISTS books_out;

CREATE VIEW books_out AS
SELECT b.title, COUNT(*) AS copies_out
FROM loans AS l
JOIN books AS b ON b.id = l.book_id
WHERE l.returned_on IS NULL
GROUP BY b.id;

SELECT * FROM books_out;

Can a view use another view?

Yes. A view can read from tables, from other views, or both. The example builds loan_status on top of the loan details and adds a CASE that labels each loan returned, overdue or on loan. Reports can then filter on a clear status column instead of repeating the date logic.

Keep the chain short. Two layers are easy to follow. Five layers of views built on views make it hard to work out where a number came from, and a bug in the bottom view spreads upwards unnoticed. When a query on a view looks wrong, run the view's own SELECT, then the view below it, the same way you'd check each step of a WITH query.

CREATE VIEW loan_details AS
SELECT l.id, b.title, m.name AS member_name, l.due_on, l.returned_on
FROM loans AS l
JOIN books AS b ON b.id = l.book_id
JOIN members AS m ON m.id = l.member_id;

CREATE VIEW loan_status AS
SELECT title, member_name, due_on,
  CASE
    WHEN returned_on IS NOT NULL THEN 'returned'
    WHEN due_on < '2026-10-01' THEN 'overdue'
    ELSE 'on loan'
  END AS status
FROM loan_details;

SELECT status, COUNT(*) AS loans
FROM loan_status
GROUP BY status;

How is a view different from WITH?

A WITH step names a query for the length of one statement. When the statement ends, the name is gone. A view names a query for the life of the database, so every later statement, and every other person or program using the database, can use it.

Reach for WITH when you are building one report and want to read it step by step. Reach for a view when the same definition will be needed again tomorrow, by someone else, or by a dashboard. A good pattern is to prove a query out as a WITH, then promote the useful step into a view once it has settled.

What should I watch for with views?

When an AI suggests a view, read its SELECT exactly as you would any query, because every mistake in it reaches every report that uses the view.

• Dates baked into the definition. The overdue view here uses a fixed '2026-10-01' so the lesson stays repeatable. In a real app, use date('now') so the view moves with the calendar.
• SELECT * inside a view. In SQLite the column list is worked out when the view is read, but other databases fix it when the view is created, so a new column in the table may not appear. List the columns you mean.
• ORDER BY inside a view. Sort in the query that reads the view, where the order is guaranteed.
• Slow views. A view is only as fast as its SELECT, and querying it runs that SELECT every time. Indexes on the underlying tables still help.

Field notes

• A view is a SELECT saved in the database under a name.
• Views let you write a common join once and reuse it in many reports.
• A view stores no data, so it always shows the tables as they are right now.
• DROP VIEW removes only the saved query and leaves the tables untouched.

Easy to get wrong

SQLite views are read-only. Write to the real table, and the view picks up the new row the next time it is read.

A view's columns take their names from the SELECT. Two columns both called id make the view awkward to query. Give each column a clear alias.

SQLite doesn't support CREATE OR REPLACE VIEW. Drop the view and create it again.

Questions people ask

Does a view make queries faster?

No. Reading a view runs its SELECT, so it costs the same as running that SELECT yourself. Speed comes from indexes on the tables underneath.

Where can I see the views in a database?

SELECT name, sql FROM sqlite_master WHERE type = 'view'; lists every view with the SQL that defines it. Database tools such as DB Browser for SQLite show them in their own list.

What happens to a view if I drop a table it uses?

SQLite lets you drop the table, and the view stays. The next time someone reads the view, they get a 'no such table' error. Drop or rebuild dependent views at the same time.

Can I put an index on a view?

Not in SQLite. Index the columns of the underlying tables that the view filters or joins on, and queries through the view benefit.