Views

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.

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

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_name	is_updatable	security_type	definer
v_book_catalogue	NO	DEFINER	admin@localhost
v_book_stock	YES	DEFINER	admin@localhost
v_customer_summary	NO	DEFINER	admin@localhost
v_customers_public	YES	DEFINER	admin@localhost
v_genre_revenue	NO	DEFINER	admin@localhost
v_in_stock_books	YES	DEFINER	admin@localhost
v_my_orders	NO	DEFINER	admin@localhost
v_recent_orders	YES	DEFINER	admin@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

Concept	Syntax / rule	Notes
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.