Views

A view is a stored SELECT that behaves like a virtual table.

Concept

A view is a stored SELECT that behaves like a virtual table. MySQL views store no data. MySQL has no native materialized views; they are emulated with summary tables refreshed by events or application jobs.

Why It Matters

Views simplify complex queries, provide a stable interface, and restrict access to sensitive columns.

Syntax

CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user] [SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(cols)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

ALTER VIEW view_name AS select_statement;
DROP VIEW [IF EXISTS] view_name;
SHOW CREATE VIEW view_name;

Example

How MySQL executes View text is merged into the outer query Uses base-table indexes; view stays updatable

View result is materialized into an internal temporary table first

Needed for aggregates/DISTINCT/UNION/LIMIT; not updatable UNDEFINED (default) MySQL chooses (prefers MERGE) 15.2 DEFINER and SQL SECURITY

SQL SECURITY DEFINER (default): the view runs with the privileges of the account that created it — useful for controlled access, but if that account is dropped the view fails ("The user specified as a definer does not exist").

SQL SECURITY INVOKER: the caller's privileges are checked.

15.3 Updatable views and CHECK OPTION A view is updatable if it maps one-to-one to base rows (no aggregates, DISTINCT, GROUP BY, HAVING, UNION, subquery in the SELECT list, and uses MERGE).

15.4 Emulating materialized views

CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.order_id, o.order_date, c.name AS customer_name, c.country, o.status, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT customer_name, SUM(total_amount) AS spent
FROM v_order_summary
WHERE status <> 'CANCELLED'
GROUP BY customer_name
ORDER BY spent DESC;
-- Ben Carter 105000.00, Ananya Gupta 93900.00, Diya Patel 1600.00

-- Security view: no salary
CREATE SQL SECURITY DEFINER VIEW v_employee_directory AS
SELECT emp_id, first_name, last_name, email, dept_id, job_title FROM employees;
GRANT SELECT ON eh_company.v_employee_directory TO 'eh_readonly'@'%';

CREATE VIEW v_india_customers AS
SELECT customer_id, name, city, country FROM customers WHERE country = 'India'
WITH CHECK OPTION;

UPDATE v_india_customers SET city = 'Navi Mumbai' WHERE customer_id = 1;   -- OK
UPDATE v_india_customers SET country = 'UK' WHERE customer_id = 1;
-- ERROR 1369 (HY000): CHECK OPTION failed 'eh_company.v_india_customers'

CREATE TABLE mv_monthly_revenue (
    month       CHAR(7)       PRIMARY KEY,
    orders      INT           NOT NULL,
    revenue     DECIMAL(14,2) NOT NULL,
    refreshed_at DATETIME     NOT NULL
);

-- Refresh (run by an EVENT every hour — Module 22)
REPLACE INTO mv_monthly_revenue (month, orders, revenue, refreshed_at)
SELECT DATE_FORMAT(order_date, '%Y-%m'), COUNT(*), SUM(total_amount), NOW()
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

Expected Result

SELECT * FROM v_employee_directory WHERE dept_id = 3;

+--------+------------+-----------+--------------------+---------+------------+
| emp_id | first_name | last_name | email              | dept_id | job_title  |
+--------+------------+-----------+--------------------+---------+------------+
|      6 | Arjun      | Das       | arjun.das@corp.com |       3 | HR Manager |
+--------+------------+-----------+--------------------+---------+------------+

Common Mistakes

Expecting views to be faster (they are not cached).

SELECT * in views (column list frozen at creation; later changes to base tables are not reflected).

Orphaned DEFINER accounts after user cleanup.

TEMPTABLE views on large tables, which cannot use indexes on the outer filter.

Best Practices

Explicit columns, v_ prefix, shallow nesting.

Consider SQL SECURITY INVOKER unless the view is a deliberate privilege boundary.

Use summary tables + events for materialization.

Interview Relevance

Medium: "Does MySQL have materialized views?", "MERGE vs TEMPTABLE?", "What is WITH CHECK OPTION?", "DEFINER vs INVOKER?".

Practice Questions

Create a view of employees with their department and manager names.

Create an updatable view with CHECK OPTION for PENDING orders.

Emulate a materialized view of product sales, refreshed by a scheduled event.