SQL › Save a Query for Later

Views and Materialized Views

A view is a query saved under a name. You query it like a table, and its query runs every time. A materialized view saves the query's rows instead, so it only changes when you refresh it.

Syntax

Order of execution: FROM view_name runs the view's saved query → your WHERE → SELECT → ORDER BY. A materialized view skips the first step and reads the rows saved at its last refresh.

Code
CREATE VIEW view_name AS
SELECT ...;
CREATE OR REPLACE VIEW view_name AS SELECT ...;      -- the old columns first, new ones at the end
DROP VIEW view_name;
 
CREATE MATERIALIZED VIEW mv_name AS SELECT ...;
REFRESH MATERIALIZED VIEW mv_name;                    -- run the query again and save the new rows
DROP MATERIALIZED VIEW mv_name;

Every database has views. Materialized views are in PostgreSQL and Oracle; SQL Server's closest is an indexed view, which stays up to date by itself, and MySQL has none.

Examples

Create and use a view

Save a join once, then read it like a table.

Code
CREATE VIEW v_review_details AS
SELECT r.review_id, p.product_name, c.full_name, r.rating
FROM reviews r
JOIN products p ON p.product_id = r.product_id
JOIN customers c ON c.customer_id = r.customer_id;
SELECT product_name, full_name FROM v_review_details WHERE rating = 5 ORDER BY product_name;

A view is always current

A view holds no rows, so a change to its table shows up at once.

Code
CREATE VIEW v_low_stock AS
SELECT product_id, product_name, stock FROM products WHERE stock < 50;
UPDATE products SET stock = 10 WHERE product_id = 1;
SELECT * FROM v_low_stock ORDER BY product_id;        -- product 1 is in it now

Read and replace a view

pg_get_viewdef shows a view's saved query in PostgreSQL. PostgreSQL's CREATE OR REPLACE must keep the existing columns with the same names, order and types; it can only add columns at the end. MySQL and Oracle replace the whole view, and SQL Server writes CREATE OR ALTER VIEW.

Code
SELECT pg_get_viewdef('v_top_customers');
CREATE VIEW v_city_customers AS
SELECT city, COUNT(*) AS customers FROM customers GROUP BY city;
CREATE OR REPLACE VIEW v_city_customers AS
SELECT city, COUNT(*) AS customers, SUM(loyalty_points) AS points FROM customers GROUP BY city;
-- Fails: the first column must still be city
CREATE OR REPLACE VIEW v_city_customers AS
SELECT COUNT(*) AS customers, city, SUM(loyalty_points) AS points FROM customers GROUP BY city;

Rename a view or its columns

ALTER VIEW renames a view, or one of its columns, without touching the tables underneath. This is PostgreSQL's form: MySQL renames a view with RENAME TABLE, SQL Server with sp_rename and Oracle with RENAME, and to rename a column they create the view again.

Code
CREATE VIEW v_city_customers AS
SELECT city, COUNT(*) AS customers FROM customers GROUP BY city;
 
ALTER VIEW v_city_customers RENAME COLUMN customers TO customer_count;   -- the view's column, not the table's
ALTER VIEW v_city_customers RENAME TO v_customers_per_city;
SELECT city, customer_count FROM v_customers_per_city ORDER BY city;

SELECT * is fixed when the view is made

A view keeps the column list it had when it was created. A column added to the table later doesn't show up in a SELECT * view until you create the view again; this is true in every major database.

Code
CREATE VIEW v_all_categories AS SELECT * FROM categories;
ALTER TABLE categories ADD COLUMN banner_url TEXT;
SELECT * FROM v_all_categories;                                         -- no banner_url yet
 
CREATE OR REPLACE VIEW v_all_categories AS SELECT * FROM categories;    -- now it has banner_url
SELECT * FROM v_all_categories;

Change rows through a view

A simple view can take INSERT, UPDATE and DELETE, and the change goes to its table. In PostgreSQL, simple means one table, and no DISTINCT, GROUP BY, aggregates, window functions, WITH, LIMIT or UNION.

Other databases have similar rules. MySQL, SQL Server and Oracle can also change one table of a view that joins several.

Code
CREATE VIEW v_toys AS
SELECT product_id, product_name, category_id, price FROM products WHERE category_id = 8;
UPDATE v_toys SET price = price + 100 WHERE product_name = 'RC Stunt Car';   -- products changes too
 
CREATE VIEW v_cities AS SELECT DISTINCT city FROM customers;
-- Fails: with DISTINCT, a row of the view isn't one row of the table
UPDATE v_cities SET city = 'Bombay' WHERE city = 'Mumbai';

WITH CHECK OPTION

A row added through a view can fall outside the view's WHERE, so it lands in the table but disappears from the view. WITH CHECK OPTION refuses such rows; it's standard SQL.

Code
CREATE VIEW v_toys AS
SELECT product_id, product_name, category_id, price FROM products WHERE category_id = 8
WITH CHECK OPTION;
 
INSERT INTO v_toys (product_name, category_id, price) VALUES ('Puzzle Cube', 8, 299);   -- a toy: allowed
-- Fails: category 2 is outside the view
INSERT INTO v_toys (product_name, category_id, price) VALUES ('Science Kit', 2, 799);

Views that hide data

A view can also protect data. Give a user GRANT SELECT on the view only, and they can read its rows without any access to the tables behind it, such as the customers' phone numbers. The lab doesn't allow GRANT.

The view's query isn't always hidden, though: PostgreSQL lets every user read a view's definition.

A materialized view saves its rows

It keeps the old rows until you refresh it. Reading it is fast because the rows are already worked out; the cost moves to the refresh, which runs the whole query again.

Use one for a slow query over data that changes rarely, such as a report that many people read all day and that is refreshed every night. Skip it when every read must show the latest data.

In PostgreSQL, the rows change only when you run REFRESH, often from a scheduled job. Oracle can also refresh a materialized view by itself, on a schedule or after each commit.

Code
CREATE MATERIALIZED VIEW mv_city_customers AS
SELECT city, COUNT(*) AS customers FROM customers GROUP BY city;
INSERT INTO customers (full_name, email, city) VALUES ('Ravi Rao', 'ravi.rao@example.com', 'Goa');
SELECT customers FROM mv_city_customers WHERE city = 'Goa';     -- still the old count
REFRESH MATERIALIZED VIEW mv_city_customers;
SELECT customers FROM mv_city_customers WHERE city = 'Goa';     -- now it counts Ravi

Remove a view

Use the DROP that matches what it is.

Code
CREATE MATERIALIZED VIEW mv_cities AS SELECT DISTINCT city FROM customers;
-- Fails: mv_cities is a materialized view, not a view
DROP VIEW mv_cities;
DROP MATERIALIZED VIEW mv_cities;

Remember

• A view stores its query, not rows; reading it always gives current data.
• In PostgreSQL, CREATE OR REPLACE VIEW keeps the old columns' names, order and types; add new columns at the end.
• Read a view's query before you change it, with pg_get_viewdef in PostgreSQL.
• A materialized view is only as fresh as its last REFRESH MATERIALIZED VIEW.
• SELECT * in a view is fixed when it's created; WITH CHECK OPTION stops rows that would fall outside the view.

Practice

1. A View of the Shop Catalog (Easy)
2. Add a Column to Monthly Sales (Medium)
3. A Snapshot That Goes Stale (Medium)