MySQL Views: How to Create, Replace, Update, and Drop Virtual Tables

A view is a stored SELECT query that acts like a virtual table. It pulls data from real tables every time you query it — no data is stored in the view itself. Useful for simplifying complex joins, restricting column access, and reusing query logic.

Table of Contents

• What is a View
• CREATE VIEW
• CREATE OR REPLACE VIEW (Replace View)
• Update View — Two Meanings
• DROP VIEW
• Important MySQL Notes

What is a View

A view is a saved SQL query you can query like a real table. Every time you access it, MySQL runs the underlying query against the base tables.

Why use views:

• Simplify complex queries — join multiple tables once, query the view afterwards.
• Restrict access — expose only specific columns to specific users.
• Reusability — write the query once, reference it by name.

CREATE VIEW

Syntax

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

Basic Example

CREATE VIEW active_customers AS
SELECT id, name, email, signup_date
FROM customers
WHERE status = 'active';

Query the view like a table:

SELECT * FROM active_customers;
SELECT name, email FROM active_customers WHERE signup_date > '2025-01-01';

View with a Join

CREATE VIEW order_summary AS
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.quantity * p.price AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

Now a simple query replaces a four-table join:

SELECT * FROM order_summary WHERE customer_name = 'John Doe';

Check Existing Views

SHOW FULL TABLES WHERE Table_Type = 'VIEW';

See how a view is defined:

SHOW CREATE VIEW active_customers;

CREATE OR REPLACE VIEW (Replace View)

Replaces an existing view's definition, or creates it if it doesn't exist. No need to DROP first.

Syntax

CREATE OR REPLACE VIEW view_name AS
new_select_query;

Example — Add a Column to the View

Current active_customers has id, name, email, signup_date. You need phone too:

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, phone, signup_date
FROM customers
WHERE status = 'active';

If the view exists — replaced. If not — created.

In MySQL, ALTER VIEW view_name AS ... does the same thing. CREATE OR REPLACE VIEW is more widely used and portable.

Update View — Two Meanings

"Update view" in MySQL can mean two different things. Both are covered below.

1. Update the View Definition

Changing what the view shows → use CREATE OR REPLACE VIEW (covered above).

2. Update Data Through a View

Modifying actual table data by running UPDATE, INSERT, or DELETE on the view.

This only works on updatable views. A view is updatable when it:

• References only one table (no joins).
• Does not use GROUP BY, DISTINCT, HAVING, window functions, or aggregate functions (SUM, COUNT, AVG, etc.).
• Does not use subqueries in the SELECT list.
• Does not use UNION or UNION ALL.

Updatable View Example

CREATE VIEW active_customers AS
SELECT id, name, email, status
FROM customers
WHERE status = 'active';

Update rows:

UPDATE active_customers
SET email = 'newemail@example.com'
WHERE id = 5;

Insert rows:

INSERT INTO active_customers (id, name, email, status)
VALUES (10, 'Jane Doe', 'jane@example.com', 'active');

Delete rows:

DELETE FROM active_customers WHERE id = 10;

Non-Updatable View Example

CREATE VIEW customer_order_count AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

GROUP BY + aggregate function → not updatable. UPDATE or INSERT on this view will fail.

WITH CHECK OPTION

When you insert or update through a view, MySQL lets you add rows that the view wouldn't show back. Example: an active_customers view only shows status = 'active'. You could INSERT a row with status = 'inactive' — it goes into the table but disappears from the view.

To prevent this:

CREATE VIEW active_customers AS
SELECT id, name, email, status
FROM customers
WHERE status = 'active'
WITH CHECK OPTION;

Now any INSERT or UPDATE that violates WHERE status = 'active' is rejected.

MySQL supports two levels:

WITH LOCAL CHECK OPTION      -- checks only this view's WHERE clause
WITH CASCADED CHECK OPTION   -- checks this view AND any views it's built on (default)

WITH CHECK OPTION only applies to modifications through the view. Direct table changes are unaffected.

DROP VIEW

Removes the view. Underlying tables and their data are untouched.

Syntax

DROP VIEW [IF EXISTS] view_name;

Examples

DROP VIEW active_customers;

Drop multiple at once:

DROP VIEW IF EXISTS active_customers, order_summary, customer_order_count;

IF EXISTS prevents an error if a view doesn't exist.

Cascading views: If view B is built on view A, dropping A will break B. B still exists but errors when queried.

Important MySQL Notes

ORDER BY in views is ignored

MySQL ignores ORDER BY in a view definition unless LIMIT is also used.

-- This ORDER BY is ignored
CREATE VIEW recent_users AS
SELECT * FROM users ORDER BY created_at DESC;

-- This will NOT return rows in any guaranteed order
SELECT * FROM recent_users;

-- Apply ORDER BY in the outer query instead
SELECT * FROM recent_users ORDER BY created_at DESC;

Views do not improve performance

Views are not pre-computed or cached. Every query on a view runs the underlying SQL. They simplify your queries — they do not speed them up.

(MySQL does not support materialized views. For caching, look into query cache or application-level caching instead.)

View dependencies

If you alter or drop a base table that a view references, the view may break. Use CHECK TABLE view_name to verify a view's integrity.