SQL Views
A view is a saved query that you can SELECT from like a table. It hides complexity and standardises a "right" way to look at the data.
Create
SQL
CREATE VIEW active_customers AS SELECT id, name, email FROM customers WHERE active = 1;
Use it
SQL
SELECT * FROM active_customers WHERE email LIKE '%@example.com';
Replacing a view
SQL
CREATE OR REPLACE VIEW active_customers AS SELECT id, name, email, last_login FROM customers WHERE active = 1;
Views are virtual
By default, a view is just a stored SELECT — every time you query it, the engine runs the underlying query. No extra storage, always fresh.
Materialised views
If the underlying query is expensive and the data doesn't need to be live, store the result in a materialised view:
SQL
-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY 1;
REFRESH MATERIALIZED VIEW monthly_revenue;
Drop
SQL
DROP VIEW active_customers;
Tip: Use views to encapsulate a tricky filter ("active and not in dev") so app code can rely on it. Don't let a view stack 5-deep on another view, though — debugging the resulting plan gets painful fast.
Example
Example
CREATE VIEW active_customers AS SELECT id, name, email FROM customers WHERE active = 1;Try it Yourself »
Exercise
Refresh a materialised view in Postgres.
MATERIALIZED VIEW monthly_revenue;
Seven letters; the recompute keyword.
Discussion
Loading…