Views — SQL Tutorial with Examples

What You'll Learn

A VIEW is a saved SQL query stored in the database that acts as a virtual table. It doesn't store data itself — it re-runs the underlying query each time it's accessed. Use views for: security (hide sensitive columns), simplifying complex joins, and providing a stable interface while underlying tables evolve.

Syntax

CREATE VIEW view_name AS SELECT ...;
SELECT * FROM view_name;
CREATE OR REPLACE VIEW view_name AS SELECT ...;
DROP VIEW view_name;

Example

-- A view that packages Topic 15's LEFT JOIN + Topic 9's GROUP BY:
CREATE VIEW customer_summary AS
SELECT
  c.id, c.name, c.email,
  COUNT(o.id)               AS order_count,
  COALESCE(SUM(o.total), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.email;

-- Use it like a table
SELECT * FROM customer_summary WHERE order_count = 0;   -- Maike

-- View with a join (security: no salary ever leaves)
CREATE VIEW order_overview AS
SELECT o.id, c.name, c.email, o.total, o.status, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Beyond the Basics

Data in play — reference tables for this topic

-- customers (from Topic 6): Karl, Ines, Omar, Sofia, Maike
-- orders (from Topic 7): customer 1 ×2, 2, 3, 4 — Maike has none

Views are saved queries, not saved results

customer_summary re-runs its whole LEFT JOIN + GROUP BY every time you query it — the view stores the definition, never the rows. That is why querying a view can be just as slow as the underlying query. It is also why customer_summary always reflects live data: Maike shows up with lifetime_value 0 because Topic 15's LEFT JOIN keeps customers without orders.

SELECT * FROM customer_summary;
-- Karl | 2 | 670.00
-- Ines | 1 | 89.90
-- Omar | 1 | 59.50
-- Sofia| 1 | 120.00
-- Maike| 0 | 0.00   <- exists only because of LEFT JOIN + COALESCE

A view is a macro, not a cache. If the definition is expensive and freshness can lag, that is what MATERIALIZED views are for.

Views are a security wall

The example's second view deliberately omits nothing sensitive — but imagine employee_overview without salary. Grant access to the VIEW, not the table, and consumers can join, filter, and report without ever being able to read the hidden columns. This is row- and column-level access control without a single line of application code.

CREATE VIEW employee_directory AS
SELECT id, name, department_id
FROM employees;                      -- no salary, no hire_date

GRANT SELECT ON employee_directory TO 'reporting_app';
-- The app can query the directory; salary is unreachable —
-- it cannot even see that the column exists

Expose the columns a consumer needs through a view, grant on the view — the table itself stays sealed.

The stability contract: views decouple consumers from schema changes

Ten app versions query customers; then the team splits the table into customers + customer_profiles. Without a view, that rename is a migration across every codebase. With a view shaped like the old table, you rewrite the view once (CREATE OR REPLACE) and every consumer keeps working.

-- Old table shape, kept alive as a view after a redesign:
CREATE OR REPLACE VIEW customer_summary AS
SELECT
  c.id, c.name, p.email,          -- email now lives elsewhere
  COUNT(o.id)               AS order_count,
  COALESCE(SUM(o.total), 0) AS lifetime_value
FROM customers c
JOIN customer_profiles p ON p.customer_id = c.id
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, p.email;

Views are an API. Consumers code against the view's shape, and the schema behind it is free to evolve — the same decoupling your application gets from interfaces.

Common Mistakes

Views don't automatically perform better than the raw query — they still execute the full query each time (unless it's a Materialized View in PostgreSQL). Don't confuse views with caching. Updating data through a view has restrictions.

Interview Tips

Mention Materialized Views (PostgreSQL) — they store the result and must be refreshed manually. Good for expensive analytics queries that don't need real-time data. Also: views can grant users access to specific columns, hiding sensitive data like salary.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. A VIEW stores…?

2. How do views provide security?

3. The 'stability contract' of a view means…?

4. What is a materialized view?

5. Why does customer_summary (LEFT JOIN + GROUP BY) still show Maike with lifetime_value 0?

6. When can you INSERT through a view?

Practice

Return the name and salary of every employee earning 90000 or more — the rows a 'high earners' view would expose.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

What is a materialized view and when should I use one?

A view that DOES store its result, physically. Queries read precomputed rows — fast — but the data is frozen until you REFRESH it. Right for expensive aggregates where slightly-stale is acceptable (dashboards, leaderboards); wrong for anything that must be current, like account balances.

Can I INSERT or UPDATE through a view?

Sometimes — simple single-table views without aggregates, DISTINCT, or GROUP BY are updatable, and the change lands in the base table. Once a view aggregates or joins, the engine can't know which underlying rows a change means, so writes are rejected (PostgreSQL offers INSTEAD OF triggers as an escape hatch).