Views: a saved query as a virtual table
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Five reports repeat the same long join of orders, customers and products. Copying it everywhere is error-prone, and one change means five edits. How do you write it once and reuse it?
CREATE VIEW and querying it
A view is a named, saved SELECT that you query like a table. It stores no rows of its own: every time you use it, MySQL runs the saved query on the current data.
A long join is needed again and again. Save it once under a name, and read it like a table from then on.
Example 1 · Create a view and query it like a table
CREATE VIEW order_summary ASSELECT o.order_id, o.order_date, o.status, c.name AS customer, c.city, SUM(oi.quantity * oi.unit_price) AS order_totalFROM orders oJOIN customers c ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idGROUP BY o.order_id, o.order_date, o.status, c.name, c.city;
-- Filter and sort the viewSELECT customer, order_date, status, order_totalFROM order_summaryWHERE city = 'Chennai'ORDER BY order_date;
-- Group the viewSELECT status, COUNT(*) AS orders, SUM(order_total) AS valueFROM order_summaryGROUP BY statusORDER BY value DESC;+------------+------------+-----------+-------------+ | customer | order_date | status | order_total | +------------+------------+-----------+-------------+ | Ravi Kumar | 2025-01-18 | DELIVERED | 1947.00 | | Meena Iyer | 2025-02-10 | CANCELLED | 69900.00 | | Ravi Kumar | 2025-03-15 | SHIPPED | 1897.00 | | Ravi Kumar | 2025-06-01 | PLACED | 4897.00 | +------------+------------+-----------+-------------+ +-----------+--------+-----------+ | status | orders | value | +-----------+--------+-----------+ | DELIVERED | 8 | 164663.00 | | CANCELLED | 2 | 112890.00 | | PLACED | 3 | 25495.00 | | SHIPPED | 2 | 6295.00 | +-----------+--------+-----------+
What this code does
CREATE VIEWprints nothing; it only saves the query. The twoSELECTs then useorder_summaryexactly like a table.- Neither query mentions a join or
SUM(quantity * unit_price). The view hides that logic, so the readers stay short and cannot get it wrong. - The column names come from the view's
SELECT, including the aliasescustomerandorder_total.
Example 2 · A view stores no data
CREATE VIEW order_summary ASSELECT o.order_id, o.order_date, o.status, c.name AS customer, SUM(oi.quantity * oi.unit_price) AS order_totalFROM orders oJOIN customers c ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idGROUP BY o.order_id, o.order_date, o.status, c.name;
SELECT COUNT(*) AS imran_ordersFROM order_summaryWHERE customer = 'Imran Khan';
-- Imran places his first order: a power bankINSERT INTO orders (order_id, customer_id, order_date, status)VALUES (16, 10, '2025-06-03', 'PLACED');INSERT INTO order_items (order_id, product_id, quantity, unit_price)VALUES (16, 7, 1, 2199.00);
SELECT order_id, order_date, status, order_totalFROM order_summaryWHERE customer = 'Imran Khan';+--------------+ | imran_orders | +--------------+ | 0 | +--------------+ +----------+------------+--------+-------------+ | order_id | order_date | status | order_total | +----------+------------+--------+-------------+ | 16 | 2025-06-03 | PLACED | 2199.00 | +----------+------------+--------+-------------+
What this code does
- Before the inserts the view has no order for Imran. Nobody refreshes or rebuilds the view after the inserts.
- The next
SELECTon the view runs the saved query again on the current tables, so the new order appears at once with its total.
Create a view with CREATE VIEW name AS SELECT .... After that, SELECT ... FROM name works as if name were a table: you can filter it, sort it, group it and join it.
A view is also called a virtual table. The pain it removes is repetition. ShopLite's "order with customer name and total" needs three tables, a join and a GROUP BY. Without a view, every report and every screen of the app repeats that same block of SQL, and a fix must be made in every copy. SHOW FULL TABLES lists views next to tables with the type VIEW. Every column of a view needs a unique name: selecting p.name and c.name together fails with error 1060, Duplicate column name 'name', until you add aliases such as c.name AS category.
Predict the output
Predict through a view
Prices under 500: Clean Code 499, Atomic Habits 399, Rubik's Cube 299.
Round 1 of 2
SELECT. MySQL has no stored (materialized) views; when a summary must be stored, you keep it in a normal table.Why views help applications and reports
A view gives everyone a stable, simple name for a piece of logic. An application, a reporting tool and an analyst can all read order_summary without knowing which tables and joins sit behind it.
Example 3 · Views for a support team and a report
-- Support staff see names and cities, never email or phoneCREATE VIEW customer_directory ASSELECT customer_id, name, city, joined_onFROM customers;
-- Revenue rules in one place: cancelled orders do not countCREATE VIEW monthly_revenue ASSELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT o.order_id) AS orders, SUM(oi.quantity * oi.unit_price) AS revenueFROM orders oJOIN order_items oi ON oi.order_id = o.order_idWHERE o.status <> 'CANCELLED'GROUP BY month;
SELECT name, city FROM customer_directory WHERE city = 'Delhi' ORDER BY name;
SELECT month, orders, revenue FROM monthly_revenue ORDER BY month;+-------------+-------+ | name | city | +-------------+-------+ | Kabir Singh | Delhi | | Rahul Verma | Delhi | +-------------+-------+ +---------+--------+----------+ | month | orders | revenue | +---------+--------+----------+ | 2025-01 | 2 | 22544.00 | | 2025-02 | 2 | 66037.00 | | 2025-03 | 3 | 72083.00 | | 2025-04 | 3 | 24594.00 | | 2025-05 | 2 | 6298.00 | | 2025-06 | 1 | 4897.00 | +---------+--------+----------+
What this code does
customer_directorysimply leaves outemailandphone. WithGRANT SELECTon the view alone, a support login cannot read those columns at all.monthly_revenueholds the rule "cancelled orders do not count". Every dashboard that reads the view uses the same rule, so two reports can never disagree about revenue.- An application can show the monthly chart with one short query and no joins.
If the tables change later, you update the view once and the readers keep working. Views also protect data. A support team may need customer names and cities but not email addresses or phone numbers. A view that leaves those columns out, together with GRANT SELECT ON practice.customer_directory TO 'support'@'localhost';, gives that user access to the view only, never the full table. Views can be built on other views, so a monthly_revenue view can use order_summary. Keep such stacks short (two or three levels), because a problem deep down is harder to find.
- One place for a complex join used by many queries.
- Hide private columns such as email, phone or salary.
- Give reports a stable name while tables change.
- Keep business rules such as "cancelled orders do not count" in one place.
CREATE OR REPLACE VIEW and DROP VIEW
CREATE OR REPLACE VIEW creates a view, or replaces its definition if it already exists. Plain CREATE VIEW on an existing name fails with error 1050, as below. ALTER VIEW name AS SELECT ... also changes an existing view.
CREATE VIEW product_list AS SELECT product_id, name FROM products;CREATE VIEW product_list AS SELECT product_id, name, price FROM products;ERROR 1050 (42S01): Table 'product_list' already exists
Example 4 · Change a view, then drop it
CREATE VIEW product_list ASSELECT product_id, name, priceFROM products;
DESCRIBE product_list;
-- Add the category name: replace the definitionCREATE OR REPLACE VIEW product_list ASSELECT p.product_id, p.name, p.price, c.name AS categoryFROM products pLEFT JOIN categories c ON c.category_id = p.category_id;
DESCRIBE product_list;
SELECT name, price, categoryFROM product_listWHERE price > 60000ORDER BY price;
DROP VIEW IF EXISTS product_list;+------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | product_id | int | NO | | 0 | | | name | varchar(80) | NO | | NULL | | | price | decimal(10,2) | NO | | NULL | | +------------+---------------+------+-----+---------+-------+ +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | product_id | int | NO | | 0 | | | name | varchar(80) | NO | | NULL | | | price | decimal(10,2) | NO | | NULL | | | category | varchar(50) | YES | | NULL | | +------------+---------------+------+-----+---------+-------+ +------------------+----------+----------+ | name | price | category | +------------------+----------+----------+ | HP Victus Gaming | 64990.00 | Laptops | | iPhone 15 | 69900.00 | Mobiles | +------------------+----------+----------+
What this code does
DESCRIBEworks on a view and shows its columns and types, taken from the tables underneath.CREATE OR REPLACE VIEWswapped the definition in one step, and the view gained acategorycolumn.c.name AS categoryavoids error 1060, becausep.namealready uses the namename.DROP VIEW IF EXISTSremoves only the saved query; theproductstable and its 15 rows are untouched.
DROP VIEW name removes a view; DROP VIEW IF EXISTS name does not complain when it is missing. Dropping a view never touches the data in the tables underneath. Note that DROP TABLE does not remove a view: MySQL treats it as an unknown table.
Because a view depends on its tables, renaming or dropping a column it uses breaks it. The next query on the view fails with error 1356, View 'practice.budget_products' references invalid table(s) or column(s)..., where practice is your database name. Fix the view with CREATE OR REPLACE VIEW after any such table change.
Fix the error
Fix the CREATE VIEW error
The view product_list already exists. Pick the line that replaces it.
Change the saved query of an existing view.
Error
ERROR 1050 (42S01): Table 'product_list' already exists
Which views you can update
An updatable view is a view in which every row can be traced back to exactly one row of one table. You can then UPDATE through it, and the change goes into the base table.
An updatable view is one where MySQL can trace every row of the view back to exactly one row of one table. You can then UPDATE (and often INSERT or DELETE) through the view, and the change goes into the base table.
Example 5 · Updating through a simple view
CREATE VIEW budget_products ASSELECT product_id, name, price, stockFROM productsWHERE price < 1000WITH CHECK OPTION;
-- 5 more Rubik's Cubes arrive: update through the viewUPDATE budget_productsSET stock = stock + 5WHERE product_id = 14;
-- The change is in the real tableSELECT product_id, name, price, stockFROM productsWHERE product_id = 14;+------------+--------------+--------+-------+ | product_id | name | price | stock | +------------+--------------+--------+-------+ | 14 | Rubik's Cube | 299.00 | 75 | +------------+--------------+--------+-------+
What this code does
budget_productsreads one table with plain columns and aWHERE, so each view row maps to exactly oneproductsrow. That makes it updatable.- The
UPDATEwent through the view intoproducts: the stock rose from 70 to 75. WITH CHECK OPTIONwould refuseSET price = 1500through this view (error 1369), because the row would no longer matchprice < 1000.
Example 6 · A grouped view is read-only
CREATE VIEW category_counts ASSELECT category_id, COUNT(*) AS productsFROM productsGROUP BY category_id;
SELECT category_id, productsFROM category_countsWHERE category_id IN (2, 3)ORDER BY category_id;
-- Which product rows should change to make Laptops show 5? MySQL cannot know.UPDATE category_counts SET products = 5 WHERE category_id = 3;+-------------+----------+ | category_id | products | +-------------+----------+ | 2 | 3 | | 3 | 2 | +-------------+----------+ ERROR 1288 (HY000): The target table category_counts of the UPDATE is not updatable
What this code does
- Reading the grouped view works as usual.
- One row of
category_countsstands for several products, so there is no single base row to change. MySQL refuses with error 1288. - To change the counts, change the
productstable itself; the view follows automatically.
Simple views qualify: one table, plain columns, a WHERE.
A view that uses GROUP BY, aggregates such as COUNT or SUM, DISTINCT, UNION, or a correlated subquery in the SELECT list is read-only. One row of it may stand for many table rows, so MySQL cannot know what to change. An UPDATE fails with error 1288, The target table ... of the UPDATE is not updatable, and an INSERT with error 1471. Add WITH CHECK OPTION to stop changes that would make a row fall out of the view. On budget_products (price below 1000), setting a price to 1500 through the view fails with error 1369, CHECK OPTION failed 'practice.budget_products'.
Find the bug
Find the wrong line
Each statement fails or breaks a rule. Click the wrong line.
category_counts groups the products with GROUP BY.
Round 1 of 2Next step
Check your understanding
12 quick questions