Views: Saved Queries as Virtual Tables
Give a name to a query and reuse it like a table
Big Idea: A view is a stored
SELECT statement that you can query as if it were a table. It holds no data of its own — every time you read from it, MySQL runs the saved query against the real (base) tables and hands you fresh results. Views let you package a complex join or filter behind one clean name, expose only the columns and rows a user should see, and keep repeated logic in a single place.
By the end you will be able to:
- create a view with
CREATE VIEW ... AS SELECT ..., - explain how a view simplifies access by hiding joins and filters,
- use a view to secure access by exposing only some columns and rows,
- tell an updatable view from a read-only one,
- anticipate view maintenance pitfalls such as
SELECT *and changing base tables.
1. Our example schema
Every query below runs against one small shop database: a customers table and an orders table linked by customer_id. Create it once and we will build views on top of it.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
vip BOOLEAN
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(8,2),
status VARCHAR(20),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES
(1, 'Ava', 'London', TRUE),
(2, 'Ben', 'Paris', FALSE),
(3, 'Chen', 'London', TRUE),
(4, 'Dina', 'Berlin', FALSE);
INSERT INTO orders VALUES
(101, 1, '2026-01-05', 120.00, 'shipped'),
(102, 2, '2026-01-06', 45.50, 'shipped'),
(103, 1, '2026-02-11', 300.00, 'pending'),
(104, 3, '2026-02-14', 80.00, 'cancelled'),
(105, 3, '2026-03-02', 210.00, 'shipped'),
(106, 4, '2026-03-08', 15.00, 'shipped');Four customers, six orders. Keep this data in mind — the result tables below come straight from it.