Learn what SQL views are, how they work internally, and when to use them. Includes visual diagrams, practical examples, views vs tables comparison, and common mistakes.
Jump into the SQLNoir case files and put these tips to work.
A SQL view is a saved query that acts like a virtual table. You query it like any regular table, but it stores no data of its own. Every time you SELECT from a view, the database runs the underlying query fresh.
A view is a named SELECT query stored in the database. When you query it, the database swaps in the saved query, runs it, and hands you the results. No data is duplicated. No extra storage is consumed. The view is just a definition.
How a view executes: every query re-runs the underlying SELECT
Here is a simple example. Suppose you have a suspects table and you frequently need to see only those with no alibi:
CREATE VIEW prime_suspects AS SELECT name, last_seen, motive FROM suspects WHERE alibi IS NULL;
Now prime_suspects behaves like a table. Query it with a plain SELECT.
SELECT * FROM prime_suspects;
Behind the scenes, the database runs the full SELECT from the view definition and returns the filtered rows.
prime_suspectsname, last_seen, alibisuspectsalibi IS NULLAnatomy of a CREATE VIEW statement
Views get more useful as queries get more complex. Below are three progressively richer examples.
CREATE VIEW public_suspects AS SELECT suspect_id, name, last_seen FROM suspects;
Hides sensitive columns like confidential_notes and informant_name.
CREATE VIEW open_cases AS SELECT case_id, title, status, detective_id FROM cases WHERE status = 'open';
Only returns rows matching the condition. Closed cases are excluded every time.
CREATE VIEW case_evidence_summary AS SELECT c.case_id, c.title AS case_title, COUNT(e.evidence_id) AS evidence_count, MAX(e.collected_date) AS latest_evidence FROM cases c LEFT JOIN evidence e ON c.case_id = e.case_id GROUP BY c.case_id, c.title;
Joins two tables and aggregates evidence per case. Now anyone can run SELECT * FROM case_evidence_summary without knowing the JOIN logic.
Views use the same SELECT, WHERE, and JOIN skills you practice in SQLNoir's detective cases. If you want to sharpen those fundamentals, try solving a mystery.
From the outside, views and tables look identical. You query both with SELECT. The difference is what happens behind the scenes.
| Feature | Table | View |
|---|---|---|
| Stores data | Yes, on disk | No, just a saved query |
| Takes up storage | Yes | No (except materialized) |
| Shows current data | Shows last INSERT/UPDATE | Always current (re-runs query) |
| Supports INSERT/UPDATE | Yes | Sometimes (with restrictions) |
| Can have indexes | Yes | No (except indexed views) |
| Can have constraints | Yes (PK, FK, CHECK) | No |
| Queried with SELECT | Yes | Yes, identical syntax |
Views vs Tables: the key differences
-- Querying a table SELECT name, last_seen FROM suspects WHERE alibi IS NULL; -- Querying a view (same syntax, same result) SELECT name, last_seen FROM prime_suspects;
The caller does not know (or care) whether prime_suspects is a table or a view. That is the power of abstraction.
Wrap a five-table JOIN in a view and reuse it by name. Analysts run SELECT * FROM monthly_report instead of pasting 30 lines of SQL.
Grant users access to a view that hides sensitive columns. They see what they need, nothing more.
SELECT * FROM suspects; -- Exposes: confidential_notes, informant_name, phone_taps
•All columns visible including sensitive data
•No way to restrict without database permissions on each column
SELECT * FROM public_suspects; -- Only shows: suspect_id, name, last_seen, alibi
•Sensitive columns hidden by design
•Users query the view, never touch the base table
Views as a security layer
Restructure your underlying tables without breaking every query that depends on them. Update the view definition and downstream queries keep working.
Bake calculations into a view so every report uses the same formula. revenue always equals price * quantity * (1 - discount), not whatever each analyst remembers.
Rename or split a table? Create a view with the old name that maps to the new structure. Legacy code never notices.
Some views are read-only. Others let you modify data through them, and the changes land in the base table. The rules depend on how the view is defined.
If you answered NO to any step, the view is read-only
CREATE VIEW active_suspects AS SELECT suspect_id, name, status FROM suspects WHERE status = 'active'; -- This works: the view is a simple single-table filter INSERT INTO active_suspects (suspect_id, name, status) VALUES (42, 'Eddie Vance', 'active');
The row is inserted into the suspects base table.
CREATE VIEW active_suspects AS SELECT suspect_id, name, status FROM suspects WHERE status = 'active' WITH CHECK OPTION; -- This FAILS: status = 'cleared' doesn't match the WHERE INSERT INTO active_suspects (suspect_id, name, status) VALUES (43, 'Maria Chen', 'cleared'); -- ERROR: CHECK OPTION failed
Without CHECK OPTION, that row would slip into the base table but vanish from the view. CHECK OPTION prevents the “vanishing row” problem.
Can you JOIN multiple tables, filter with WHERE, and find the killer? The same SQL skills that power views are all you need.
Start InvestigationViews are easy to change. Use CREATE OR REPLACE VIEW to update a definition without dropping and recreating.
CREATE OR REPLACE VIEW prime_suspects AS SELECT name, last_seen, motive, phone_number FROM suspects WHERE alibi IS NULL;
Added phone_number to the existing view. Dependent queries keep working.
DROP VIEW IF EXISTS prime_suspects;
IF EXISTS prevents an error when the view has already been removed.
CREATE VIEW case_overview AS SELECT * FROM cases;
•If someone adds a column to cases, the view might break or expose unexpected data
•Column order can shift, breaking dependent code
CREATE VIEW case_overview AS SELECT case_id, title, status, detective_id FROM cases;
•Table changes don't affect the view
•Clear contract: you know exactly what columns are exposed
Always list columns explicitly in view definitions
Building views that reference other views creates layers the optimizer struggles with. Each layer re-expands, and performance degrades quickly. If you find yourself stacking three or more views, flatten the logic into a single query or use a materialized view instead.
Views do not cache results. The database re-executes the underlying query every time. If you need cached, precomputed results, look at materialized views (PostgreSQL, Oracle) or indexed views (SQL Server).
Views with JOINs, GROUP BY, DISTINCT, or aggregate functions are read-only. Attempting an UPDATE or INSERT will throw an error. If you need to modify data through a view, keep it simple: one base table, no aggregates.
The core concept is the same everywhere, but syntax and advanced features differ.
| Feature | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|
| CREATE OR REPLACE VIEW | Yes | Yes | No (use ALTER VIEW) | Yes |
| Materialized Views | No (workarounds) | Yes (REFRESH) | Indexed Views | Yes (REFRESH) |
| WITH CHECK OPTION | Yes | Yes | Yes | Yes |
| Updatable Views | Limited | Limited | INSTEAD OF triggers | INSTEAD OF triggers |
| View Dependencies | SHOW CREATE VIEW | pg_views catalog | sys.sql_modules | ALL_VIEWS dictionary |
SQL Views feature matrix across major databases
Q1.Does a SQL view store data on disk?
Q2.Which of these makes a view NOT updatable?
Q3.What does WITH CHECK OPTION do?
Q4.How do you modify a view definition in SQL Server?
Ready to practice the SQL behind views?
You now understand SQL views, from simple definitions to cross-database gotchas. The next step? Practice writing the SELECT, JOIN, and WHERE queries that views are built on. SQLNoir's 6 detective cases give you a real database to query, a mystery to solve, and zero boring textbook exercises.
Start Your Investigation →A view is a saved SELECT query you can treat like a table. Example: CREATE VIEW active_cases AS SELECT * FROM cases WHERE status = 'open'; Then query it: SELECT * FROM active_cases; The view runs the query fresh each time.
No. Standard views do not cache data or speed up queries. The database runs the underlying SELECT every time. For performance gains, look at materialized views (PostgreSQL, Oracle) or indexed views (SQL Server), which store results on disk.
Sometimes. Simple views on a single table without GROUP BY, DISTINCT, or aggregates are usually updatable. Complex views with JOINs or aggregations are read-only. Use WITH CHECK OPTION to prevent invisible rows.
A table stores data physically on disk. A view stores only a query definition. When you SELECT from a view, the database runs the saved query and returns fresh results. Views take no storage space (except materialized views).
No. AI can generate CREATE VIEW statements, but you still need to understand what views are, when to use them vs tables, and their limitations around updatability and performance. AI is a tool that works best when you understand the concepts it is working with.