Views

A view is a stored query, not stored data#

CREATE VIEW v_order_totals AS
SELECT o.id AS order_id, o.customer_id, o.status, o.ordered_at,
       SUM(oi.qty * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.customer_id, o.status, o.ordered_at;

SELECT * FROM v_order_totals WHERE total > 500;

Nothing is saved except the definition. Each time you query the view, MySQL merges the definition into your query (the MERGE algorithm) or builds a temporary table (TEMPTABLE), then runs it. So a view is a convenience and a security tool, not a cache.

Why use them#

• Reuse: one canonical definition of "order total" instead of five slightly different copies.
• Simplification: hide a five-table join behind a name.
• Access control: grant SELECT on a view that omits sensitive columns, without granting the base table.

app ──▶ v_customer_public ──▶ customers (name, city)
                               email, phone: hidden

Changing and dropping#

CREATE OR REPLACE VIEW v_order_totals AS ...;
ALTER VIEW v_order_totals AS ...;
DROP VIEW IF EXISTS v_order_totals;
SHOW CREATE VIEW v_order_totals\G

Updatable views#

A simple view over one table, without aggregates, DISTINCT, GROUP BY, or UNION, can be written through:

CREATE VIEW v_pune_customers AS
SELECT id, name, email, city FROM customers WHERE city = 'Pune'
WITH CHECK OPTION;

UPDATE v_pune_customers SET city = 'Delhi' WHERE id = 1;
-- ERROR 1369: CHECK OPTION failed

WITH CHECK OPTION blocks writes that would make a row disappear from the view. Without it, that UPDATE succeeds and the row silently leaves the view, which is rarely intended.

Where views go wrong#

Stacking views on views produces queries nobody can reason about. EXPLAIN shows the flattened mess. A view containing GROUP BY forces aggregation of the whole set before your outer WHERE can help in some cases, so a filter that looks selective may not be.

Also, adding a column to a base table doesn't update a view defined with SELECT *. The star expands when the view is created. Never use * in a view.

Common mistakes#

• Treating a view as if it were cached.
• Using SELECT * in a view definition.
• Nesting many layers of views.
• Forgetting WITH CHECK OPTION on an updatable filter view.

Practice#

Create v_order_totals and use it to list the top three customers by total spend. Then create a view that exposes only id, name, and city from customers, and confirm a user granted access only to that view can't read emails. Finally, create a filtered view with WITH CHECK OPTION and try an update that violates it.