Loading…
Loading…
SQL tutorial · Advanced
⏱️ 10 mins read
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.
CREATE VIEW view_name AS SELECT ...;
SELECT * FROM view_name;
CREATE OR REPLACE VIEW view_name AS SELECT ...;
DROP VIEW view_name;-- 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;customers (from Topic 6): Karl, Ines, Omar, Sofia, Maike orders (from Topic 7): customer 1 ×2, 2, 3, 4 — Maike has none
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 + COALESCEThe 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 existsTen 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 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.
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.
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?
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 →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.
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).