MySQL Views: CREATE VIEW, ALTER VIEW and DROP VIEW Explained

A view is a named, stored SELECT. It holds no data of its own. Each time you query the view, MySQL runs its query.

query.sql

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(60) NOT NULL, city VARCHAR(20) NOT NULL, password_hash VARCHAR(60) NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(8,2) NOT NULL, status VARCHAR(10) NOT NULL, ordered_on DATE NOT NULL);
INSERT INTO customers VALUES (1, 'Maya', 'maya@example.com', 'Pune', 'x1'), (2, 'Rohan', 'rohan@example.com', 'Delhi', 'x2'), (3, 'Tara', 'tara@example.com', 'Kochi', 'x3');
INSERT INTO orders VALUES (1, 1, 680.00, 'paid', '2026-05-02'), (2, 1, 1299.00, 'paid', '2026-05-20'), (3, 2, 145.00, 'shipped', '2026-05-21'), (4, 2, 220.00, 'cancelled', '2026-06-20');

CREATE VIEW customer_summary AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status <> 'cancelled' THEN o.total END), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

SELECT * FROM customer_summary ORDER BY id;

Output

OUTPUT

+----+-------+-------+--------+---------+
| id | name  | city  | orders | spent   |
+----+-------+-------+--------+---------+
|  1 | Maya  | Pune  |      2 | 1979.00 |
|  2 | Rohan | Delhi |      2 |  145.00 |
|  3 | Tara  | Kochi |      0 |    0.00 |
+----+-------+-------+--------+---------+
3 rows in set

You query the view as if it were a table. You can filter, sort and join it:

query.sql

SQL

SELECT name, spent FROM customer_summary WHERE spent > 0 ORDER BY spent DESC;

Output

OUTPUT

+-------+---------+
| name  | spent   |
+-------+---------+
| Maya  | 1979.00 |
| Rohan |  145.00 |
+-------+---------+
2 rows in set

Syntax

Text

PLAIN

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

CREATE OR REPLACE VIEW changes an existing view in one step.
ALTER VIEW also changes it. DROP VIEW [IF EXISTS] removes it. Neither touches any data.

query.sql

SQL

CREATE OR REPLACE VIEW customer_summary AS
SELECT c.id, c.name, c.city, COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status <> 'cancelled' THEN o.total END), 0) AS spent,
       MAX(o.ordered_on) AS last_order_on
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

SELECT name, last_order_on FROM customer_summary ORDER BY id;

Output

OUTPUT

+-------+---------------+
| name  | last_order_on |
+-------+---------------+
| Maya  | 2026-05-20    |
| Rohan | 2026-06-20    |
| Tara  | NULL          |
+-------+---------------+
3 rows in set

What views are good for

1. Reuse a complicated query. Write the joins and calculations once, and give them a name.

2. Hide complexity and rename columns. A report writer can query customer_summary without knowing the join rules.

3. Restrict what users can see (security). Give a user access to the view and not to the table. The view can leave out sensitive columns and rows:

query.sql

SQL

CREATE VIEW customer_public AS
SELECT id, name, city FROM customers;

SELECT * FROM customer_public ORDER BY id;

Output

OUTPUT

+----+-------+-------+
| id | name  | city  |
+----+-------+-------+
|  1 | Maya  | Pune  |
|  2 | Rohan | Delhi |
|  3 | Tara  | Kochi |
+----+-------+-------+
3 rows in set

The email and password_hash columns are not in the view. A user granted SELECT on customer_public only (Privileges) cannot read them.

4. Keep an interface stable. If you rename a table, you can keep the old name as a view for old code while you migrate.

A view is not stored data, and not a speed-up

A view does not make a query faster. MySQL still runs the underlying query. A view of a slow query is a slow view.
It does not cache results. MySQL has no materialised views. For stored results, use a summary table (Summary Tables).
Indexes of the underlying tables are used. You cannot create an index on a view.

ALGORITHM: MERGE or TEMPTABLE

Algorithm	How MySQL runs a query on the view
`MERGE`	Merges the view’s `SELECT` into your query. Fast, indexes are used, and the view can be updatable. Not possible when the view has `GROUP BY`, aggregates, `DISTINCT`, `LIMIT`, `UNION` or subqueries in the select list
`TEMPTABLE`	Runs the view’s query first into a temporary table, then reads it. Slower, never updatable
`UNDEFINED` (default)	MySQL chooses: `MERGE` if possible, otherwise `TEMPTABLE`

query.sql

SQL

CREATE VIEW paid_orders AS SELECT id, customer_id, total FROM orders WHERE status = 'paid';

SELECT view_name_check.table_name, view_name_check.is_updatable
FROM (SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = 'learn') AS view_name_check
ORDER BY view_name_check.table_name;

Output

OUTPUT

+------------------+--------------+
| TABLE_NAME       | IS_UPDATABLE |
+------------------+--------------+
| customer_public  | YES          |
| customer_summary | NO           |
| paid_orders      | YES          |
+------------------+--------------+
3 rows in set

paid_orders (a plain filter) is updatable, so MySQL merges it. customer_summary (grouping) is not, so MySQL uses a temporary table when you query it.

Views on views and dependencies

A view can select from another view. Deep chains are hard to read and tune. Keep it to one or two levels.

If you drop or change a table that a view uses, the view breaks (an error appears when you query it, not when the table is changed). Check dependencies before schema changes:

query.sql

SQL

SELECT table_name AS view_name, view_definition IS NOT NULL AS has_definition, security_type FROM information_schema.views WHERE table_schema = 'learn' ORDER BY table_name;

Output

OUTPUT

+------------------+----------------+---------------+
| view_name        | has_definition | SECURITY_TYPE |
+------------------+----------------+---------------+
| customer_public  |              1 | DEFINER       |
| customer_summary |              1 | DEFINER       |
| paid_orders      |              1 | DEFINER       |
+------------------+----------------+---------------+
3 rows in set

Definer and SQL SECURITY

A view runs with the privileges of its definer (the user who created it) by default (SQL SECURITY DEFINER), or those of the invoker (SQL SECURITY INVOKER). A definer view lets a limited user read data they have no direct right to, which is the point of the “restrict” pattern. Use it deliberately, and prefer a dedicated low-privilege account as the definer in production, so that a view is not tied to a person who may leave.

Show and inspect

query.sql

SQL

SHOW CREATE VIEW customer_public\G

Output

OUTPUT

*************************** 1. row ***************************
                View: customer_public
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `customer_public` AS select `customers`.`id` AS `id`,`customers`.`name` AS `name`,`customers`.`city` AS `city` from `customers`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set

Views and ORDER BY / SELECT *

Avoid SELECT * in a view definition. The column list is fixed when the view is created, so new columns of the table do not appear (and renamed ones break it).
An ORDER BY inside a view is ignored if the query using the view has its own ORDER BY, and is unreliable otherwise. Sort in the outer query.

When not to use views

To hide a slow query: you will still have a slow query.
For logic that changes constantly: views are schema objects that need migrations.
As a way to “cache”: use a table.

lightbulb

Name views by what they show

customer_summary, paid_orders or v_customer_summary make the purpose clear. Pick a naming convention and keep it.