SQL Views Explained: What They Are, How They Work, and When to Use Them

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.

What Is a SQL View?

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.

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;

SELECT * FROM prime_suspects;

Creating Views: Syntax and Examples

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;

CREATE VIEW open_cases AS
SELECT case_id, title, status, detective_id
FROM cases
WHERE status = 'open';

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;

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.

Views vs Tables: What's the Difference?

From the outside, views and tables look identical. You query both with SELECT. The difference is what happens behind the scenes.

-- 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;

5 Practical Use Cases for Views

1. Simplifying complex queries

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.

2. Security and access control

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

SELECT * FROM public_suspects;
-- Only shows: suspect_id, name, last_seen, alibi

3. Data abstraction

Restructure your underlying tables without breaking every query that depends on them. Update the view definition and downstream queries keep working.

4. Consistent reporting

Bake calculations into a view so every report uses the same formula. revenue always equals price * quantity * (1 - discount), not whatever each analyst remembers.

5. Backward compatibility

Rename or split a table? Create a view with the old name that maps to the new structure. Legacy code never notices.

Updatable Views: When You Can INSERT, UPDATE, and DELETE

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.

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');

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

Modifying and Dropping Views

Views 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;

DROP VIEW IF EXISTS prime_suspects;

Common Mistakes with SQL Views

1. Using SELECT * in view definitions

CREATE VIEW case_overview AS
SELECT * FROM cases;

CREATE VIEW case_overview AS
SELECT case_id, title, status, detective_id
FROM cases;

2. Nesting views on top of views

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.

3. Assuming views improve performance

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).

4. Trying to UPDATE through complex views

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.

Views Across Databases: MySQL, PostgreSQL, SQL Server, Oracle

The core concept is the same everywhere, but syntax and advanced features differ.

Test Your Knowledge

FAQ