Chapter 7

Chapter 7 — Views

A view is a saved SELECT statement stored in the database under a name. From the outside it looks and behaves exactly like a table — you can SELECT from it, JOIN it, and in many cases INSERT, UPDATE, and DELETE through it. The SELECT itself is stored; the data is not (unless you use a materialised view, which MySQL doesn't support natively).

Views serve three main purposes in production databases:

  • Simplify complex queries — give a multi-join report query a short, stable name so application code doesn't repeat it.
  • Abstract schema changes — rename a column in the underlying table; update the view definition; application code sees no change.
  • Control access — expose only certain columns or rows to a database user without granting access to the base tables directly.
Views store definitions, not data. Every time you query a view, MySQL re-executes the underlying SELECT. There is no automatic caching. For expensive queries run repeatedly, consider a scheduled job that writes to a real summary table instead.

1. CREATE VIEW — Basic Syntax

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

A simple view — full book catalogue with author and publisher

This is a query you'd otherwise paste into every report. Give it a name:

CREATE OR REPLACE VIEW v_book_catalogue AS SELECT b.book_id, b.title, b.isbn, b.price, CONCAT(a.first_name, ' ', a.last_name) AS author, p.name AS publisher, b.published_year FROM books b JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id JOIN publishers p ON p.publisher_id = b.publisher_id;

From this point on, application code just writes:

-- Looks exactly like querying a table SELECT * FROM v_book_catalogue WHERE publisher = 'Penguin'; SELECT * FROM v_book_catalogue WHERE price < 10.00 ORDER BY title; -- Join a view to a real table SELECT vc.title, vc.author, COUNT(r.review_id) AS reviews, ROUND(AVG(r.rating), 1) AS avg_rating FROM v_book_catalogue vc LEFT JOIN reviews r ON r.book_id = vc.book_id GROUP BY vc.book_id, vc.title, vc.author ORDER BY avg_rating DESC;

OR REPLACE vs DROP + CREATE

CREATE OR REPLACE VIEW atomically swaps the definition — no downtime, no gap where the view doesn't exist. Always prefer it over the two-step DROP then CREATE pattern.

-- Safe update of a view definition CREATE OR REPLACE VIEW v_book_catalogue AS SELECT b.book_id, b.title, b.isbn, b.price, b.stock_quantity, -- ← new column added CONCAT(a.first_name, ' ', a.last_name) AS author, p.name AS publisher, b.published_year FROM books b JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id JOIN publishers p ON p.publisher_id = b.publisher_id;

Named column list

You can rename columns in the view definition rather than using aliases inside the SELECT:

CREATE OR REPLACE VIEW v_customer_summary (customer_id, full_name, email, total_orders, lifetime_value) AS SELECT c.customer_id, CONCAT(c.first_name, ' ', c.last_name), c.email, COUNT(DISTINCT o.order_id), SUM(oi.quantity * oi.unit_price) FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id GROUP BY c.customer_id, c.first_name, c.last_name, c.email;

2. Updatable vs Non-Updatable Views

MySQL can execute INSERT, UPDATE, and DELETE statements through a view — as long as the view is simple enough that MySQL can unambiguously map the operation back to a single base table row.

✓ Updatable — view can be modified
  • SELECT from a single base table
  • No DISTINCT
  • No aggregate functions (SUM, COUNT, AVG …)
  • No GROUP BY or HAVING
  • No UNION or UNION ALL
  • No subqueries in SELECT list
  • No window functions
  • All NOT NULL columns in base table either included or have defaults
✗ Non-updatable — read-only
  • JOIN across multiple tables
  • DISTINCT keyword present
  • Aggregate functions in SELECT
  • GROUP BY or HAVING clause
  • UNION or UNION ALL
  • Subquery in SELECT list or WHERE referencing the same table
  • Window functions (OVER clause)
  • Derived columns with no mapping to a base column

Example — an updatable view for book stock management

CREATE OR REPLACE VIEW v_book_stock AS SELECT book_id, title, stock_quantity, price FROM books; -- UPDATE through the view — affects the books table directly UPDATE v_book_stock SET stock_quantity = stock_quantity - 1 WHERE book_id = 42; -- INSERT through the view — inserts into books INSERT INTO v_book_stock (title, stock_quantity, price) VALUES ('A New Title', 50, 14.99); -- DELETE through the view DELETE FROM v_book_stock WHERE book_id = 99;
Check whether a view is updatable: SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = DATABASE();

WITH CHECK OPTION — enforce the view's WHERE on writes

Without CHECK OPTION, you can UPDATE a row through a view so that it no longer satisfies the view's WHERE clause — the row silently "disappears" from the view after the update. WITH CHECK OPTION blocks that:

-- View that only shows in-stock books CREATE OR REPLACE VIEW v_in_stock_books AS SELECT book_id, title, stock_quantity FROM books WHERE stock_quantity > 0 WITH CHECK OPTION; -- This UPDATE is blocked — would make the row invisible to this view UPDATE v_in_stock_books SET stock_quantity = 0 WHERE book_id = 5; -- ERROR 1369: CHECK OPTION failed 'bookshop.v_in_stock_books' -- This UPDATE is fine — row stays visible UPDATE v_in_stock_books SET stock_quantity = 10 WHERE book_id = 5;
CASCADED vs LOCAL check option: When a view is built on another view, WITH CASCADED CHECK OPTION (the default) enforces the WHERE clause of the current view and all underlying views. WITH LOCAL CHECK OPTION enforces only the current view's WHERE. Use CASCADED unless you have a specific reason not to — it is safer.

3. Column-Level Access Control via Views

MySQL's GRANT system works at the table level by default. Views are the standard way to expose only specific columns to a database user — the equivalent of column-level permissions without needing to grant them explicitly.

Scenario — a reporting user who must not see customer PII

-- Base table has sensitive columns: email, phone, address -- Create a view that omits them CREATE OR REPLACE VIEW v_customers_public AS SELECT customer_id, first_name, last_name, city, country, created_at FROM customers; -- email, phone, address deliberately excluded -- Grant the reporting user access to the VIEW only, not the base table GRANT SELECT ON bookshop.v_customers_public TO 'reporter'@'%'; -- Do NOT grant: GRANT SELECT ON bookshop.customers TO 'reporter'@'%';

Row-level security — filter rows per user

Views can also restrict which rows a user sees using CURRENT_USER() or a lookup table:

-- Each customer can only see their own orders CREATE OR REPLACE VIEW v_my_orders AS SELECT o.order_id, o.order_date, oi.book_id, b.title, oi.quantity, oi.unit_price FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN books b ON b.book_id = oi.book_id JOIN customers c ON c.customer_id = o.customer_id WHERE c.email = CURRENT_USER(); -- maps DB login to customer email
SQL SECURITY — DEFINER vs INVOKER: By default MySQL views run with SQL SECURITY DEFINER — the view executes with the privileges of the user who created it, not the user querying it. This means the querying user doesn't need access to the base tables at all, which is exactly what you want for access control views. Change to SQL SECURITY INVOKER if you want the caller's own privileges to apply.
-- Explicit DEFINER (default behaviour, but make it visible) CREATE OR REPLACE DEFINER = 'admin'@'localhost' SQL SECURITY DEFINER VIEW v_customers_public AS SELECT customer_id, first_name, last_name, city, country FROM customers;

4. View Algorithm — MERGE, TEMPTABLE, UNDEFINED

The ALGORITHM clause tells MySQL how to execute the view internally. In most cases you leave it as UNDEFINED and trust the optimiser — but knowing the options matters when performance is a concern.

MERGE
MySQL merges the view's SELECT with the outer query into a single query. The optimiser can use indexes on the base tables. This is what you want for most views.
TEMPTABLE
The view's SELECT runs first and its result is stored in a temporary table. The outer query runs against that. Indexes on base tables cannot help the outer WHERE — can be slow. Required for views with GROUP BY, DISTINCT, aggregate functions, or UNION.
UNDEFINED
Default. MySQL chooses MERGE if possible, falls back to TEMPTABLE when the view requires it. Almost always the right choice.
-- Force MERGE for a simple view (optimiser usually picks this anyway) CREATE OR REPLACE ALGORITHM = MERGE VIEW v_recent_orders AS SELECT order_id, customer_id, order_date, total_amount FROM orders WHERE order_date >= CURDATE() - INTERVAL 30 DAY; -- TEMPTABLE required here (GROUP BY makes it non-updatable too) CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v_genre_revenue AS SELECT g.name AS genre, SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT oi.order_id) AS orders FROM order_items oi JOIN books b ON b.book_id = oi.book_id JOIN book_genres bg ON bg.book_id = b.book_id JOIN genres g ON g.genre_id = bg.genre_id GROUP BY g.genre_id, g.name;
TEMPTABLE views cannot be updated and the outer WHERE clause cannot push predicates into the temp table scan — it always scans the full temp table. If you query a TEMPTABLE view with WHERE genre = 'Fiction', MySQL cannot use an index; it materialises all rows first. For large aggregation views accessed frequently, consider writing results to a real summary table on a schedule.

5. Inspecting and Managing Views

List all views in the current database

SELECT table_name AS view_name, is_updatable, security_type, definer FROM information_schema.views WHERE table_schema = DATABASE() ORDER BY table_name;
view_nameis_updatablesecurity_typedefiner
v_book_catalogueNODEFINERadmin@localhost
v_book_stockYESDEFINERadmin@localhost
v_customer_summaryNODEFINERadmin@localhost
v_customers_publicYESDEFINERadmin@localhost
v_genre_revenueNODEFINERadmin@localhost
v_in_stock_booksYESDEFINERadmin@localhost
v_my_ordersNODEFINERadmin@localhost
v_recent_ordersYESDEFINERadmin@localhost

Show a view's full definition

-- Method 1: SHOW CREATE VIEW SHOW CREATE VIEW v_book_catalogue\G -- Method 2: information_schema (gives the clean SELECT without grants) SELECT view_definition FROM information_schema.views WHERE table_schema = DATABASE() AND table_name = 'v_book_catalogue';

DESCRIBE a view — see its columns

DESCRIBE v_book_catalogue; -- or SHOW COLUMNS FROM v_book_catalogue;

Drop a view

DROP VIEW IF EXISTS v_book_catalogue; -- Drop multiple views at once DROP VIEW IF EXISTS v_book_catalogue, v_book_stock, v_customer_summary;
Always use IF EXISTS when dropping views in scripts — prevents a hard error if the view was already removed or never existed.

6. Putting It Together — A Reporting View Layer

A well-designed application often has a thin view layer that sits between the raw schema and the application code. Here's a practical set for the bookshop:

-- ── 1. Full book catalogue (read-only — multi-table join) CREATE OR REPLACE VIEW v_book_catalogue AS SELECT b.book_id, b.title, b.isbn, b.price, b.stock_quantity, CONCAT(a.first_name, ' ', a.last_name) AS author, p.name AS publisher, b.published_year FROM books b JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id JOIN publishers p ON p.publisher_id = b.publisher_id; -- ── 2. In-stock books only (updatable + check option) CREATE OR REPLACE VIEW v_in_stock AS SELECT book_id, title, stock_quantity, price FROM books WHERE stock_quantity > 0 WITH CHECK OPTION; -- ── 3. Customer lifetime value (read-only — aggregate) CREATE OR REPLACE VIEW v_customer_ltv AS SELECT c.customer_id, CONCAT(c.first_name, ' ', c.last_name) AS customer, c.email, COUNT(DISTINCT o.order_id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS lifetime_value, MAX(o.order_date) AS last_order_date FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id GROUP BY c.customer_id, customer, c.email; -- ── 4. Revenue by genre (read-only — aggregate) CREATE OR REPLACE VIEW v_genre_revenue AS SELECT g.genre_id, g.name AS genre, COUNT(DISTINCT oi.order_id) AS orders, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN books b ON b.book_id = oi.book_id JOIN book_genres bg ON bg.book_id = b.book_id JOIN genres g ON g.genre_id = bg.genre_id GROUP BY g.genre_id, g.name; -- ── 5. Low-stock alert (updatable — single table, with check) CREATE OR REPLACE VIEW v_low_stock AS SELECT book_id, title, stock_quantity FROM books WHERE stock_quantity < 5 WITH CHECK OPTION;

Now the application layer is clean

-- Homepage: featured in-stock books SELECT title, author, price FROM v_book_catalogue WHERE stock_quantity > 0 ORDER BY published_year DESC LIMIT 12; -- Restock report: books needing reorder SELECT * FROM v_low_stock ORDER BY stock_quantity; -- Marketing: top spenders this year SELECT customer, lifetime_value, total_orders, last_order_date FROM v_customer_ltv WHERE last_order_date >= '2026-01-01' ORDER BY lifetime_value DESC LIMIT 20;

Quick Reference

ConceptSyntax / ruleNotes
Create / replace CREATE OR REPLACE VIEW name AS SELECT … Atomic swap — prefer over DROP + CREATE.
Named columns CREATE VIEW name (col1, col2) AS SELECT … Alternative to aliases inside the SELECT list.
Updatable view Single base table, no DISTINCT / GROUP BY / UNION / aggregates / window functions INSERT, UPDATE, DELETE work through the view.
WITH CHECK OPTION … WHERE condition WITH CHECK OPTION Blocks writes that would make the row invisible to the view's WHERE clause.
CASCADED vs LOCAL WITH CASCADED CHECK OPTION (default) / WITH LOCAL CHECK OPTION CASCADED enforces check on all underlying views too. Safer default.
SQL SECURITY SQL SECURITY DEFINER (default) / SQL SECURITY INVOKER DEFINER runs with creator's privileges — caller needs no access to base tables. Ideal for access-control views.
ALGORITHM MERGE CREATE ALGORITHM = MERGE VIEW … Outer WHERE is pushed into base-table scan — indexes are used. Best performance.
ALGORITHM TEMPTABLE CREATE ALGORITHM = TEMPTABLE VIEW … View materialised to temp table first. Required for aggregates/UNION. Outer WHERE cannot use base-table indexes.
List views SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = DATABASE(); Also shows security_type and definer.
Show definition SHOW CREATE VIEW name\G Returns the original CREATE VIEW statement.
Drop DROP VIEW IF EXISTS name; Safe drop — no error if view doesn't exist.
No stored data Views execute their SELECT on every query For expensive aggregations queried frequently, materialise to a real table on a schedule instead.
Next: Chapter 8 — Stored procedures and functions: CREATE PROCEDURE, IN/OUT/INOUT parameters, CREATE FUNCTION, flow control, calling from application code, and when to use them vs application logic.