Views
IntermediateViews are named stored SELECT queries; updatable views allow DML on simple single-table views; use views to simplify complex joins and enforce column-level security.
Overview
A view is a named, stored SELECT statement that behaves like a virtual table. Queries against a view are transparently rewritten by the optimiser to include the view's underlying query. Views serve three main purposes: simplifying complex joins by exposing a clean interface, enforcing column-level security by exposing only permitted columns/rows, and providing a stable API layer that can be evolved independently of the base tables. MySQL views are not materialised by default — every query re-executes the underlying SELECT. Updatable views allow INSERT/UPDATE/DELETE when the view meets strict criteria: single base table, no DISTINCT, no aggregates, no subqueries in select list, no GROUP BY/HAVING. The WITH CHECK OPTION clause prevents inserts that would violate the view's WHERE condition.
Creating views and column-level security
Use CREATE OR REPLACE VIEW to create idempotently. The ALGORITHM clause controls execution: MERGE inlines the view definition and allows index use; TEMPTABLE materialises to a temp table (required for aggregates, DISTINCT, UNION). Grant SELECT on the view, not the base table, to enforce column-level access control.
-- View exposing only non-sensitive order columns (column-level security)
CREATE OR REPLACE VIEW v_orders_public AS
SELECT order_id, customer_id, order_date, status, total_amount
FROM orders
WHERE deleted_at IS NULL;
-- Grant SELECT on view only — reporting_user cannot access base table
GRANT SELECT ON shop.v_orders_public TO 'reporting_user'@'%';
-- Stacked view — joins over another view
CREATE OR REPLACE VIEW v_customer_orders AS
SELECT c.name, c.email, o.order_id, o.total_amount
FROM customers c
JOIN v_orders_public o ON c.id = o.customer_id;
-- Query view like a table
SELECT * FROM v_customer_orders WHERE total_amount > 1000;
-- Inspect view definition
SHOW CREATE VIEW v_orders_public;Updatable views and WITH CHECK OPTION
A view is updatable when it maps cleanly to a single base table with no aggregates, DISTINCT, GROUP BY, UNION, or subqueries in the select list. WITH CHECK OPTION prevents inserts or updates that would make the row invisible through the view's WHERE filter — without it you can insert rows that immediately disappear from the view.
-- Updatable view: active products only
CREATE OR REPLACE VIEW v_active_products AS
SELECT product_id, name, price, stock
FROM products
WHERE is_active = 1
WITH CHECK OPTION; -- blocks writes that violate WHERE is_active=1
-- Valid insert: is_active defaults to 1 in base table
INSERT INTO v_active_products (name, price, stock)
VALUES ('Widget', 9.99, 100);
-- Blocked update: would make row invisible through view
UPDATE v_active_products SET is_active = 0 WHERE product_id = 5;
-- ERROR 1369 (HY000): CHECK OPTION failed 'shop.v_active_products'
-- Non-updatable: aggregate view (TEMPTABLE algorithm)
CREATE VIEW v_order_totals AS
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;
-- DML on this view fails; MySQL forces ALGORITHM=TEMPTABLEView performance: MERGE vs TEMPTABLE algorithm
ALGORITHM=MERGE inlines the view definition into the outer query and allows the optimiser to push WHERE predicates into the view and use base table indexes. ALGORITHM=TEMPTABLE materialises the view to an internal temp table first, then applies the outer WHERE — preventing index use on the outer predicate. Views with aggregates, DISTINCT, or UNION always use TEMPTABLE. Use EXPLAIN to identify DERIVED in select_type, which signals TEMPTABLE materialisation.
-- Force MERGE algorithm for best performance on simple views
CREATE OR REPLACE ALGORITHM = MERGE VIEW v_active_products AS
SELECT product_id, name, price FROM products WHERE is_active = 1;
-- EXPLAIN reveals algorithm used
EXPLAIN SELECT * FROM v_active_products WHERE product_id = 5;
-- MERGE: select_type = SIMPLE, uses idx on product_id (fast)
-- TEMPTABLE: select_type = PRIMARY + DERIVED (materialized, outer predicate not pushed)
-- Aggregate view always TEMPTABLE — consider indexed views or summary tables
EXPLAIN SELECT * FROM v_order_totals WHERE customer_id = 42;
-- shows DERIVED — customer_id filter applied AFTER full materialisation
-- Alternative: replace slow aggregate view with CTE or materialized summary table
CREATE TABLE summary_customer_orders AS
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders GROUP BY customer_id;
-- Refresh periodically or use INSERT ON DUPLICATE KEY UPDATEKey Points to Remember
- 1Views are virtual tables backed by a stored SELECT — not materialised; every query re-executes the underlying SELECT
- 2Grant permissions on the view, not the base table, to enforce column- and row-level access control for specific roles
- 3Updatable views require single base table and no aggregates, DISTINCT, GROUP BY, UNION, or select-list subqueries
- 4WITH CHECK OPTION blocks inserts/updates that would make the row invisible through the view's WHERE filter
- 5ALGORITHM=MERGE inlines the view and allows index use on the outer query; ALGORITHM=TEMPTABLE blocks predicate pushdown
- 6Views with GROUP BY, aggregates, or DISTINCT always use TEMPTABLE — check EXPLAIN for DERIVED to identify this
Interview Questions
Sign in to ask AriaWhat conditions must a view satisfy to be updatable in MySQL?
What does WITH CHECK OPTION do and why is it important for updatable views?
What is the difference between ALGORITHM=MERGE and ALGORITHM=TEMPTABLE and when does each apply?
How would you use a view to enforce column-level security for a reporting role?
You have a view with GROUP BY that is slow. What does EXPLAIN show and how would you address it?
Ask Aria about Views
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.