Views

A view is a saved SELECT statement that you can query like a table. Views hide complexity (an eight-table join becomes SELECT * FROM order_summary), give a stable interface while the underlying tables change, and limit what users can see.

Sample data#

CREATE TABLE customers (
    id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(50) NOT NULL,
    city VARCHAR(20), credit_card_last4 CHAR(4)
);
CREATE TABLE orders (
    id INT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(8,2) NOT NULL,
    status VARCHAR(10) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers VALUES
    (1, 'Asha', 'asha@x.com', 'Pune', '4242'), (2, 'Rohan', 'rohan@x.com', 'Delhi', '1881'),
    (3, 'Meera', 'meera@x.com', 'Pune', NULL);
INSERT INTO orders VALUES
    (10, 1, 250.00, 'paid'), (11, 1, 90.00, 'refunded'), (12, 2, 400.00, 'paid'), (13, 2, 60.00, 'pending');

Creating and using a view#

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id)                AS paid_orders,
       COALESCE(SUM(o.total), 0)  AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name, c.city;

SELECT * FROM customer_sales WHERE city = 'Pune' ORDER BY revenue DESC;

+----+-------+------+-------------+---------+
| id | name  | city | paid_orders | revenue |
+----+-------+------+-------------+---------+
|  1 | Asha  | Pune |           1 |  250.00 |
|  3 | Meera | Pune |           0 |    0.00 |
+----+-------+------+-------------+---------+

You can filter, sort, join and aggregate a view exactly like a table. The view doesn't store rows: each query runs against the current data in customers and orders, so it's never stale:

INSERT INTO orders VALUES (14, 3, 120.00, 'paid');
SELECT name, paid_orders, revenue FROM customer_sales WHERE id = 3;

+-------+-------------+---------+
| name  | paid_orders | revenue |
+-------+-------------+---------+
| Meera |           1 |  120.00 |
+-------+-------------+---------+

Inspecting, changing and dropping views#

SHOW FULL TABLES WHERE Table_type = 'VIEW';

+----------------+------------+
| Tables_in_shop | Table_type |
+----------------+------------+
| customer_sales | VIEW       |
+----------------+------------+

• SHOW CREATE VIEW customer_sales\G shows the stored definition. MySQL expands * into explicit columns and fully qualifies names.
• CREATE OR REPLACE VIEW name AS ... changes the definition (so does ALTER VIEW).
• DROP VIEW IF EXISTS name; removes it. The underlying data is untouched.

Because SELECT * in a view is expanded when the view is created, columns added to the base table later won't appear until you recreate the view.

Views for security#

Give people what they need and nothing more. A support agent shouldn't see card digits:

CREATE VIEW customer_directory AS
SELECT id, name, city, CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked
FROM customers;

SELECT * FROM customer_directory;

+----+-------+-------+--------------+
| id | name  | city  | email_masked |
+----+-------+-------+--------------+
|  1 | Asha  | Pune  | as***@x.com  |
|  2 | Rohan | Delhi | ro***@x.com  |
|  3 | Meera | Pune  | me***@x.com  |
+----+-------+-------+--------------+

Then grant access to the view only: GRANT SELECT ON shop.customer_directory TO 'support'@'%';. By default a view runs with its definer's privileges (SQL SECURITY DEFINER), so users can read through the view without having rights on the base table. Use SQL SECURITY INVOKER if the caller's own privileges should apply instead.

Updatable views#

A simple view, one that maps each of its rows to exactly one row of one base table, can be used in INSERT, UPDATE and DELETE:

CREATE VIEW pune_customers AS
SELECT id, name, email, city FROM customers WHERE city = 'Pune';

UPDATE pune_customers SET email = 'meera@pune.in' WHERE id = 3;
SELECT id, name, email FROM customers WHERE id = 3;

+----+-------+---------------+
| id | name  | email         |
+----+-------+---------------+
|  3 | Meera | meera@pune.in |
+----+-------+---------------+

The update went straight through to customers. A view is not updatable if it uses aggregates, GROUP BY, DISTINCT, HAVING, UNION, certain subqueries, or most joins (MySQL can update one table of a join view at a time):

UPDATE customer_sales SET revenue = 0 WHERE id = 1;

ERROR 1288 (HY000): The target table customer_sales of the UPDATE is not updatable

WITH CHECK OPTION

Without it, you can update a row out of a view's filter, or insert a row the view can't even see:

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

INSERT INTO pune_customers (id, name, email, city) VALUES (4, 'Karan', 'k@x.com', 'Mumbai');

ERROR 1369 (HY000): CHECK OPTION failed 'shop.pune_customers'

WITH CHECK OPTION guarantees that every row written through the view still satisfies its WHERE clause. That makes views a useful guard rail, for example a view per tenant in a multi-tenant app.

Performance#

MySQL processes views with one of two algorithms:

• MERGE: the view's SQL is merged into your query, as if you'd written it inline. Your extra WHERE conditions can still use indexes on the base tables.
• TEMPTABLE: the view's result is built into a temporary table first, then your query reads it. MySQL must do this for views with aggregates, DISTINCT, UNION and similar.

A view is never faster than the query inside it. Stacking views on views can produce enormous queries, so check them with EXPLAIN. MySQL has no materialised views. If you need precomputed results, keep a summary table that you refresh with a scheduled event (see Triggers & scheduled events).

Common mistakes#

• Expecting new base-table columns to appear in a SELECT * view. Recreate the view.
• Dropping or renaming a base table column a view uses. The view becomes invalid. CHECK TABLE view_name; reports broken views.
• Using views as a performance tool. They're about convenience and security, not speed.
• A definer account that gets deleted. DEFINER views stop working if the account that created them is dropped. Create shared views with a dedicated, long-lived account.

What's next#

You can now build powerful queries on top of well-structured tables. But how do you decide which tables and columns to have in the first place? That's normalisation and schema design.