Views in MySQL

What Is a View in MySQL?

A view is a named query definition that can be referenced like a table in SELECT statements. It can expose selected columns, calculate expressions, combine tables, or present aggregates through a stable interface. A normal MySQL view is not a separately refreshed materialized result: base data remains in its tables.

Verified Orders Lab

DROP TABLE IF EXISTS orders_view_lab;
DROP TABLE IF EXISTS customers_view_lab;

CREATE TABLE customers_view_lab (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(80) NOT NULL,
  city VARCHAR(50) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders_view_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  internal_note VARCHAR(100),
  FOREIGN KEY (customer_id)
    REFERENCES customers_view_lab(customer_id)
) ENGINE = InnoDB;

INSERT INTO customers_view_lab VALUES
(101,'Asha','Khurja'),(102,'Bilal','Aligarh'),
(103,'Charu','Khurja'),(104,'Deepak','Noida');

INSERT INTO orders_view_lab VALUES
(1,101,'PAID','2026-08-01',1200,'Priority customer'),
(2,101,'PENDING','2026-08-05',500,'Call after 4 PM'),
(3,102,'PAID','2026-08-03',750,NULL),
(4,103,'CANCELLED','2026-08-04',300,'Refund requested'),
(5,101,'PAID','2026-08-10',1500,NULL),
(6,102,'PENDING','2026-08-11',900,NULL),
(7,104,'PAID','2026-08-12',2200,'Do not expose'),
(8,101,'PAID','2026-08-14',650,NULL);

The examples intentionally keep internal_note out of public views. Column hiding reduces exposure through that interface, but security still requires correct GRANTs and careful DEFINER management.

Create and Query a Simple View

CREATE OR REPLACE VIEW paid_orders_v AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders_view_lab
WHERE status = 'PAID';

SELECT order_id, order_date, total_amount
FROM paid_orders_v
WHERE customer_id = 101
ORDER BY order_date;

The outer WHERE and ORDER BY are applied when the view is queried. Use explicit output columns rather than SELECT *; MySQL freezes a view definition at creation, so later base-table columns do not automatically appear.

CREATE VIEW customer_order_public_v
  (order_no, customer_no, ordered_on, amount)
AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders_view_lab;

An explicit column list creates a stable public contract. View column names must be unique, and the list length must equal the selected column count.

Join Views and Aggregate Views

CREATE OR REPLACE VIEW paid_order_details_v AS
SELECT o.order_id, c.customer_name, c.city,
       o.order_date, o.total_amount
FROM orders_view_lab AS o
JOIN customers_view_lab AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'PAID';

SELECT customer_name, order_date, total_amount
FROM paid_order_details_v
WHERE city = 'Khurja'
ORDER BY customer_name, order_date;

CREATE OR REPLACE VIEW customer_paid_summary_v AS
SELECT customer_id,
       COUNT(*) AS paid_orders,
       SUM(total_amount) AS paid_total
FROM orders_view_lab
WHERE status = 'PAID'
GROUP BY customer_id;

The aggregate view is useful for reporting but is nonupdatable because rows no longer map one-to-one to base rows. Queryability and updatability are separate properties.

Inspect, Replace, Alter and Drop Views

SHOW CREATE VIEW paid_orders_v;
SHOW FULL TABLES WHERE Table_type = 'VIEW';

SELECT TABLE_NAME, IS_UPDATABLE, DEFINER,
       SECURITY_TYPE, CHECK_OPTION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

CHECK TABLE paid_orders_v;

SHOW CREATE VIEW reveals the stored definition and security attributes. CHECK TABLE helps detect broken dependencies, for example after a referenced object is removed.

ALTER VIEW paid_orders_v AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders_view_lab
WHERE status = 'PAID' AND total_amount >= 500;

DROP VIEW IF EXISTS customer_paid_summary_v;

CREATE OR REPLACE VIEW is deployment-friendly, but replacing a public view can still break applications when columns, types or semantics change. Treat view definitions as version-controlled schema.

UNDEFINED, MERGE and TEMPTABLE Algorithms

CREATE ALGORITHM = MERGE VIEW khurja_customers_v AS
SELECT customer_id, customer_name, city
FROM customers_view_lab
WHERE city = 'Khurja';

The requested algorithm is not an unconditional performance command. Some definitions cannot be merged. Use EXPLAIN SELECT ... FROM view_name to inspect the expanded plan, and index base tables according to real predicates and joins.

DEFINER, INVOKER and Privilege Design

CREATE
SQL SECURITY DEFINER
VIEW order_public_v AS
SELECT order_id, customer_id, status,
       order_date, total_amount
FROM orders_view_lab;

SQL SECURITY DEFINER is the default: privileges for underlying objects are checked using the view's definer account. With SQL SECURITY INVOKER, underlying access is checked using the caller. In both cases, the caller needs the appropriate privilege on the view.

• Use a controlled, durable service account as definer; avoid fragile personal accounts.
• Grant users only required view operations, not unnecessary base-table access.
• Do not include sensitive columns or rows in the definition.
• Remember that a view is not a substitute for complete authorization, auditing and application validation.
• Review backups/migrations for missing or unavailable definers.

Performance, Restrictions and Faculty Checklist

• A view definition cannot reference a TEMPORARY table, and MySQL has no TEMPORARY view.
• Referenced objects must exist; dropping them can leave a view unusable.
• A view cannot have a trigger.
• ORDER BY in a view is ignored when the outer query has its own ORDER BY; request deterministic order outside.
• A LIMIT in both view and outer query has undefined combined effect; avoid such contracts.
• View abstraction can hide expensive joins, repeated scalar functions or broad result sets; inspect plans.

1. Define a narrow, stable column contract.
2. Choose security context and definer deliberately.
3. Grant least privilege on the view and underlying objects.
4. Use EXPLAIN on important outer queries.
5. Index base-table join and filter columns.
6. Test current output, NULLs, duplicates and ordering.
7. Check updatability before allowing writes.
8. Version and deploy dependency changes together.

Next, learn updatable views and CHECK OPTION, stored procedures and stored functions.

Official References

• MySQL 8.4: CREATE VIEW Statement
• MySQL 8.4: View Processing Algorithms
• MySQL 8.4: Using Views

Creation syntax, frozen definitions, algorithms, restrictions and security context were checked against the official MySQL 8.4 manual.