Views

All the statements so far have run directly on tables. This lesson’s subject is a layer that gets inserted in between: naming a query itself and using that name like a table.

In a library database the same query gets written over and over — open loans, suspended members, the count of books per branch. Every time it is written, it gets rethought, each copy can carry a separate mistake, and when the schema changes each copy has to be updated on its own. The view is the construct that removes this repetition.

A View Is a Named Query

The CREATE VIEW statement gives a query a name. When the name is used, the engine runs the stored query. A view holds no data, which is why it is also called a virtual table.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  status TEXT NOT NULL DEFAULT 'active');
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','suspended'),
                       (3,'Clara','Diaz','active');

CREATE VIEW active_member AS
  SELECT member_id, first_name, last_name FROM member WHERE status = 'active';

SELECT * FROM active_member;

UPDATE member SET status = 'active' WHERE member_id = 2;
SELECT * FROM active_member;
SQL

member_id  first_name  last_name
---------  ----------  ---------
1          Alice       Kane     
3          Clara       Diaz     
member_id  first_name  last_name
---------  ----------  ---------
1          Alice       Kane     
2          Ben         Ortiz    
3          Clara       Diaz     

The view’s definition never changed in between; only the status of one row in the base table changed, and the view reflected that change immediately. This is the fundamental difference between a view and a copied table: a copy freezes a state at one moment, a view stores the query itself.

A view behaves like a table when queried. WHERE, ORDER BY, joins, even use inside another view — all of it works. For whoever is writing the query, there is no difference between a view and a table.

A View’s Two Jobs

Views satisfy two separate needs. The first is hiding complexity: a query that joins several tables, groups rows, and uses aggregate functions gets written once, then referred to by a single name.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO member VALUES (1,'Alice','Kane'),(2,'Ben','Ortiz'),(3,'Clara','Diaz');
INSERT INTO loan VALUES
  (1,1,1,'2025-01-10','2025-01-24'),
  (2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),
  (9,7,1,'2025-05-14',NULL);

CREATE VIEW member_summary AS
  SELECT u.member_id, u.first_name, u.last_name,
         count(o.loan_id) AS total_loans,
         sum(CASE WHEN o.loan_id IS NOT NULL AND o.return_date IS NULL
                  THEN 1 ELSE 0 END) AS open_loans
  FROM member AS u
  LEFT JOIN loan AS o ON o.member_id = u.member_id
  GROUP BY u.member_id, u.first_name, u.last_name;

SELECT * FROM member_summary ORDER BY member_id;
SELECT first_name, last_name FROM member_summary WHERE open_loans > 0 ORDER BY member_id;
SQL

member_id  first_name  last_name  total_loans  open_loans
---------  ----------  ---------  -----------  ----------
1          Alice       Kane       3            1         
2          Ben         Ortiz      1            1         
3          Clara       Diaz       0            0         
first_name  last_name
----------  ---------
Alice       Kane     
Ben         Ortiz    

The second query wrote a single condition instead of writing an outer join and a grouping. A WHERE condition written on top of a view gets applied to the stored query’s result.

The o.loan_id IS NOT NULL part of the aggregate condition is a consequence of the outer join: for a member with no loans at all, the join produces a row filled with nulls, and that row would count as an open loan if the condition only looked at the blank return date. A view is also a way to write this subtlety correctly once and keep it stored.

A view’s second job is narrowing access. Whoever queries the active_member view never sees the status column on the member table. What gets shown at the column and row level is decided in the view’s definition; the reader is given the view instead of the base table. This is the most commonly used tool of the authorization that is the next lesson’s subject.

Writing to a View

If a view can be queried like a table, can it be written to like one. The answer to this question depends on both the view’s definition and the engine.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  status TEXT NOT NULL DEFAULT 'active');
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','suspended');

CREATE VIEW active_member AS SELECT member_id, first_name, last_name FROM member WHERE status = 'active';

INSERT INTO active_member (member_id, first_name, last_name) VALUES (3,'Clara','Diaz');
UPDATE active_member SET last_name = 'Kane Ortiz' WHERE member_id = 1;
DELETE FROM active_member WHERE member_id = 1;

SELECT * FROM active_member;
SQL

Parse error near line 9: cannot modify active_member because it is a view
Parse error near line 10: cannot modify active_member because it is a view
Parse error near line 11: cannot modify active_member because it is a view
member_id  first_name  last_name
---------  ----------  ---------
1          Alice       Kane     

Views are read-only in the engine used here; all three write attempts were rejected for the same reason. This is a decision that differs by engine — many engines allow direct writes to views that satisfy certain conditions.

Standard SQL defines these conditions under the heading of updatable view. Their common core is this: every row of the view must be traceable back to a single row in the base table. Constructs that break this make the view read-only:

• Joining more than one table — when a row comes from two tables, which one an update should write to is undefined.
• Grouping and aggregate functions — a row in the result summarizes more than one base row.
• Discarding duplicates and set operations — the correspondence between rows is lost.
• Columns produced by an expression in the result — how a value written to a computed column would distribute across its inputs is undefined.

What is left — a view selected from a single table, not grouped, with no duplicates discarded — is updatable as a rule. The active_member view above fits this definition and was rejected anyway: meeting the condition does not mean the engine supports it.

Building a Writable View

On engines that do not support writing directly, what a write means gets defined explicitly. A trigger set up on the view stands in for the statement and writes to the base table.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  status TEXT NOT NULL DEFAULT 'active');
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','suspended');

CREATE VIEW active_member AS SELECT member_id, first_name, last_name FROM member WHERE status = 'active';

CREATE TRIGGER active_member_insert INSTEAD OF INSERT ON active_member
BEGIN
  INSERT INTO member (member_id, first_name, last_name, status)
  VALUES (NEW.member_id, NEW.first_name, NEW.last_name, 'active');
END;

INSERT INTO active_member (member_id, first_name, last_name) VALUES (3,'Clara','Diaz');
SELECT * FROM member;
SQL

member_id  first_name  last_name  status   
---------  ----------  ---------  ---------
1          Alice       Kane       active   
2          Ben         Ortiz      suspended
3          Clara       Diaz       active   

The INSTEAD OF syntax does exactly what its name says: the insert statement is not applied to the view; instead, the statements in the body run. NEW names the row being inserted. The trigger filled in the status column, which is not part of the view — this is where the meaning of writing to the view got defined.

The detail of triggers, including the problems their implicit side effects create, belongs to the Advanced SQL course. What this lesson shows is that this is the non-standard way of making a view writable.

A Row That Disappears From the View

A writable view has a trap of its own. If the view filters rows with a condition, a row that does not satisfy that condition can still be inserted through the view — and once inserted, it does not show up in the view.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE book (
  book_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author    TEXT NOT NULL,
  branch_id INTEGER NOT NULL
);
INSERT INTO book VALUES (1,'Blindness','José Saramago',1);

CREATE VIEW central_book AS
  SELECT book_id, title, author, branch_id FROM book WHERE branch_id = 1;

CREATE TRIGGER central_book_insert INSTEAD OF INSERT ON central_book
BEGIN
  INSERT INTO book (book_id, title, author, branch_id)
  VALUES (NEW.book_id, NEW.title, NEW.author, NEW.branch_id);
END;

INSERT INTO central_book VALUES (5,'Silent House','Orhan Pamuk',3);

SELECT * FROM central_book;
SELECT * FROM book;
SQL

book_id  title      author         branch_id
-------  ---------  -------------  ---------
1        Blindness  José Saramago  1        
book_id  title         author         branch_id
-------  ------------  -------------  ---------
1        Blindness     José Saramago  1        
5        Silent House  Orhan Pamuk    3        

The insert succeeded, the row was written to the base table — but it is missing from the view that shows the central branch’s books. Whoever is using the view will not see the row they just inserted again.

Standard SQL defines an option for the view definition to cover this case: a row written through the view is required to satisfy the view’s condition. This option is not universal either.

sqlite3 :memory: <<'SQL'
CREATE TABLE member (member_id INTEGER PRIMARY KEY, status TEXT NOT NULL);
CREATE VIEW active_member AS SELECT member_id FROM member WHERE status = 'active' WITH CHECK OPTION;
SQL

Parse error near line 2: near "WITH": syntax error
  ECT member_id FROM member WHERE status = 'active' WITH CHECK OPTION;
                                      error here ---^

A clause written into the standard could not even be parsed by the engine in use. Knowing that this behavior differs by engine is a requirement for writing a portable schema: when the view’s condition needs to be preserved and the engine does not recognize this option, the check gets written by hand into the trigger’s body.

Summary

• A view holds no data, it stores the query; it reflects every change to the base table immediately and behaves like a table when queried.
• A view has two jobs: putting join and grouping complexity behind a single name, and showing the reader only a portion of the base table.
• For a view to be updatable, every one of its rows must be traceable back to a single base row; joining, grouping, discarding duplicates, and computed columns break this.
• Whether a view that meets the conditions is actually writable depends on the engine; where it is not supported, the meaning of a write is defined explicitly with an INSTEAD OF trigger.
• A row that does not satisfy the condition can be written to a filtering view, and that row will not show up in it; the standard’s option to prevent this is not available on every engine.

Next Step

A view’s second job — giving the reader a narrowed window instead of the base table — is a thought left unfinished: even when the view is offered, the base table itself is still there. A separate mechanism is needed to say who can see it. The course’s last lesson takes up object-level privileges: who a privilege is granted to, on which object, for which operation; revoking that privilege; and counting whether the privileges granted go beyond what the job requires.