🪟 Lesson 15: Views

What Is a View?

A view is a saved query that behaves like a virtual table. It doesn't store data itself — every time you query a view, MySQL runs the underlying query and returns the results as if they came from a real table.

Table vs View

Think of a view as a window into your data — it shows you a specific angle of the underlying tables without creating any copies.

Creating Views

Basic Syntax

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE conditions;
                

Example: Active Customers

-- Create a view of customers in Las Vegas
CREATE VIEW las_vegas_customers AS
SELECT id, first_name, last_name, email
FROM customers
WHERE city = 'Las Vegas';
                

That's it. The view now exists as a queryable object in your database, just like a table.

Naming Conventions

Common patterns include:

• v_customer_orders — prefix with v_
• vw_customer_orders — prefix with vw_
• customer_orders_view — suffix with _view

Any convention works — the important thing is consistency across your database so you can tell views apart from tables at a glance.

SHOW FULL TABLES WHERE Table_type = 'VIEW';
                    

SHOW CREATE VIEW las_vegas_customers;
                    

Querying Views

Once created, you use a view exactly like a table in SELECT statements:

-- Query the view just like a table
SELECT * FROM las_vegas_customers;

-- Add filters on top of the view
SELECT first_name, last_name
FROM las_vegas_customers
WHERE last_name LIKE 'S%';

-- Use it with ORDER BY, LIMIT, etc.
SELECT * FROM las_vegas_customers
ORDER BY last_name
LIMIT 10;
                

Anyone querying las_vegas_customers doesn't need to know about the WHERE city = 'Las Vegas' filter — it's baked into the view. They just write a simple SELECT and get the right data.

Views in JOINs

You can also join views with other tables or views:

-- Join the view with the orders table
SELECT
    lvc.first_name,
    lvc.last_name,
    o.id AS order_id,
    o.order_date
FROM las_vegas_customers lvc
INNER JOIN orders o ON lvc.id = o.customer_id;
                

Views with JOINs

This is where views really shine — wrapping complex multi-table JOINs into a simple, reusable name.

Order Details View

-- Save a complex JOIN as a view
CREATE VIEW v_order_details AS
SELECT
    o.id AS order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    b.title AS book_title,
    a.name AS author,
    oi.quantity,
    b.price,
    ROUND(oi.quantity * b.price, 2) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
INNER JOIN authors a ON b.author_id = a.id;
                

Now, instead of writing that 12-line query every time, anyone can just write:

-- Simple!
SELECT * FROM v_order_details;

-- Alice's orders only
SELECT * FROM v_order_details
WHERE first_name = 'Alice'
ORDER BY order_date DESC;

-- Total by author
SELECT author, SUM(line_total) AS revenue
FROM v_order_details
GROUP BY author;
                

+----------+------------+------------+-----------+---------------------+---------------+----------+-------+------------+
| order_id | order_date | first_name | last_name | book_title          | author        | quantity | price | line_total |
+----------+------------+------------+-----------+---------------------+---------------+----------+-------+------------+
|        1 | 2026-01-15 | Alice      | Johnson   | Dune                | Frank Herbert |        1 | 14.99 |      14.99 |
|        1 | 2026-01-15 | Alice      | Johnson   | 1984                | George Orwell |        2 |  9.99 |      19.98 |
|        2 | 2026-02-20 | Alice      | Johnson   | Pride and Prejudice | Jane Austen   |        1 |  7.99 |       7.99 |
|        3 | 2026-03-10 | Bob        | Smith     | Dune                | Frank Herbert |        1 | 14.99 |      14.99 |
+----------+------------+------------+-----------+---------------------+---------------+----------+-------+------------+
                    

Customer Summary View

CREATE VIEW v_customer_summary AS
SELECT
    c.id AS customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    COUNT(DISTINCT o.id) AS total_orders,
    COALESCE(SUM(oi.quantity), 0) AS items_bought,
    COALESCE(ROUND(SUM(oi.quantity * b.price), 2), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN books b ON oi.book_id = b.id
GROUP BY c.id, c.first_name, c.last_name;
                

-- Now getting a customer report is one line:
SELECT * FROM v_customer_summary ORDER BY total_spent DESC;
                

+-------------+----------------+--------------+--------------+-------------+
| customer_id | customer_name  | total_orders | items_bought | total_spent |
+-------------+----------------+--------------+--------------+-------------+
|           1 | Alice Johnson  |            2 |            4 |       42.96 |
|           2 | Bob Smith      |            1 |            1 |       14.99 |
|           3 | Carol Williams |            0 |            0 |        0.00 |
+-------------+----------------+--------------+--------------+-------------+
                    

Views with Aggregates

Views can include GROUP BY, aggregate functions, and computed columns:

Author Sales Report

CREATE VIEW v_author_sales AS
SELECT
    a.id AS author_id,
    a.name AS author,
    COUNT(DISTINCT b.id) AS books_in_catalog,
    COALESCE(SUM(oi.quantity), 0) AS copies_sold,
    COALESCE(ROUND(SUM(oi.quantity * b.price), 2), 0) AS total_revenue
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
LEFT JOIN order_items oi ON b.id = oi.book_id
GROUP BY a.id, a.name;
                

-- Quick access to author performance
SELECT * FROM v_author_sales ORDER BY total_revenue DESC;

-- Find authors with no sales
SELECT author FROM v_author_sales WHERE copies_sold = 0;
                

Monthly Revenue

CREATE VIEW v_monthly_revenue AS
SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m');
                

-- Monthly dashboard in one line
SELECT * FROM v_monthly_revenue ORDER BY month;
                

+---------+-------------+------------+---------+
| month   | order_count | items_sold | revenue |
+---------+-------------+------------+---------+
| 2026-01 |           1 |          3 |   34.97 |
| 2026-02 |           1 |          1 |    7.99 |
| 2026-03 |           1 |          1 |   14.99 |
+---------+-------------+------------+---------+
                    

Updating & Replacing Views

Need to change a view's query? Use CREATE OR REPLACE VIEW:

-- Modify the view to add the email column
CREATE OR REPLACE VIEW las_vegas_customers AS
SELECT id, first_name, last_name, email, phone
FROM customers
WHERE city = 'Las Vegas';
                

This replaces the old definition with the new one. If the view doesn't exist yet, it creates it. This is safer than DROP + CREATE because it's a single atomic operation.

ALTER VIEW

An alternative syntax that only works on existing views:

-- Only works if the view already exists
ALTER VIEW las_vegas_customers AS
SELECT id, first_name, last_name, email, phone
FROM customers
WHERE city = 'Las Vegas';
                

Updatable Views

Some views allow you to run INSERT, UPDATE, or DELETE against them, and the changes pass through to the underlying table. These are called updatable views.

When Is a View Updatable?

A view is updatable when MySQL can unambiguously trace each row in the view back to exactly one row in a single underlying table. This means the view must NOT contain:

• Aggregate functions (COUNT, SUM, AVG, etc.)
• GROUP BY or HAVING
• DISTINCT
• UNION or UNION ALL
• Subqueries in the SELECT list
• JOINs (in most cases)

Example: Updatable View

-- This view is updatable — simple filter on one table
CREATE VIEW las_vegas_customers AS
SELECT id, first_name, last_name, email
FROM customers
WHERE city = 'Las Vegas';

-- UPDATE through the view — changes the underlying table
UPDATE las_vegas_customers
SET email = 'newemail@example.com'
WHERE id = 5;

-- DELETE through the view — deletes from the underlying table
DELETE FROM las_vegas_customers WHERE id = 5;

-- INSERT through the view — inserts into the underlying table
-- (city won't be 'Las Vegas' unless you set it — see WITH CHECK OPTION below)
INSERT INTO las_vegas_customers (first_name, last_name, email)
VALUES ('Dave', 'Miller', 'dave@example.com');
                

Example: Non-Updatable View

-- This view is NOT updatable — it has GROUP BY and aggregates
CREATE VIEW v_customer_summary AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- This will fail:
UPDATE v_customer_summary SET order_count = 10 WHERE customer_id = 1;
-- Error: The target table is not updatable
                

WITH CHECK OPTION

When inserting or updating through a view, the new/modified row might not match the view's WHERE condition — making it "disappear" from the view. WITH CHECK OPTION prevents this:

CREATE VIEW las_vegas_customers AS
SELECT id, first_name, last_name, email, city
FROM customers
WHERE city = 'Las Vegas'
WITH CHECK OPTION;

-- This INSERT will FAIL because city isn't 'Las Vegas':
INSERT INTO las_vegas_customers (first_name, last_name, email, city)
VALUES ('Eve', 'Brown', 'eve@example.com', 'Denver');
-- Error: CHECK OPTION failed
                

Dropping Views

-- Drop a view
DROP VIEW v_order_details;

-- Drop only if it exists (avoids error)
DROP VIEW IF EXISTS v_order_details;

-- Drop multiple views at once
DROP VIEW IF EXISTS v_order_details, v_customer_summary, v_author_sales;
                

Dropping a view only removes the saved query definition — it does not affect the underlying tables or their data in any way.

When to Use Views

1. Simplify Complex Queries

The most common reason. Wrap a multi-table JOIN in a view and give teams a clean, simple interface:

-- Instead of writing this every time...
SELECT c.first_name, o.order_date, b.title, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;

-- ...teams can write this:
SELECT * FROM v_order_details;
                

2. Security — Restrict Access to Columns

Show users only the columns they need, hiding sensitive data:

-- Customer service team sees names and emails, not salary data
CREATE VIEW v_customer_public AS
SELECT id, first_name, last_name, email, city
FROM customers;
-- They can't see: salary, SSN, internal_notes, etc.

-- Grant access to the view only:
GRANT SELECT ON bookstore.v_customer_public TO 'support_team'@'%';
                

3. Security — Restrict Access to Rows

-- Sales team only sees their own region's data
CREATE VIEW v_west_coast_orders AS
SELECT * FROM orders
WHERE region = 'West Coast';
                

4. Backward Compatibility

If you restructure tables, a view can maintain the old "shape" so existing queries don't break:

-- Old table had a "name" column, new table has first_name + last_name
CREATE VIEW v_customers_legacy AS
SELECT id, CONCAT(first_name, ' ', last_name) AS name, email
FROM customers;

-- Old queries still work:
SELECT name, email FROM v_customers_legacy;
                

5. Reusable Report Definitions

Dashboard queries, monthly reports, KPI calculations — define once, use everywhere:

-- The finance team's monthly report is now standardized
SELECT * FROM v_monthly_revenue WHERE month = '2026-03';
                

Limitations & Gotchas

1. Views Don't Store Data

A view re-runs its query every time it's accessed. A complex view on large tables can be slow. If you need cached results, consider a materialized view (MySQL doesn't natively support these, but you can simulate one with a table + scheduled refresh).

2. No Indexes on Views

You can't add indexes to a view itself. Performance depends entirely on the indexes of the underlying tables. Make sure those base tables are properly indexed (see Lesson 14).

3. ORDER BY in Views Is Ignored

-- ORDER BY inside a view definition is mostly ignored
CREATE VIEW v_sorted_books AS
SELECT * FROM books ORDER BY title;

-- MySQL may ignore the ORDER BY — add it when querying:
SELECT * FROM v_sorted_books ORDER BY title;
                

The SQL standard says views are unordered sets. Always put ORDER BY in your outer query, not inside the view definition.

4. Renaming Columns

If the underlying table's columns are renamed, views referencing the old column names will break. This is a maintenance consideration when refactoring schemas.

5. Performance Consideration

Views built on top of other views (nested views) can create hard-to-debug performance issues. Keep the chain shallow — one level is ideal, two is acceptable, three is a code smell.

Exercises

-- 1
CREATE VIEW v_book_catalog AS
SELECT b.title, b.price, a.name AS author
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

-- 2
CREATE VIEW v_expensive_books AS
SELECT id, title, price
FROM books
WHERE price > 10.00;

-- 3
SELECT * FROM v_book_catalog WHERE author = 'George Orwell';
                        

-- 1
CREATE VIEW v_order_summary AS
SELECT
    o.id AS order_id,
    o.order_date,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    SUM(oi.quantity) AS total_items,
    ROUND(SUM(oi.quantity * b.price), 2) AS order_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
GROUP BY o.id, o.order_date, c.first_name, c.last_name;

-- 2
SELECT * FROM v_order_summary WHERE order_total > 20;

-- 3
CREATE VIEW v_never_ordered AS
SELECT c.id, c.first_name, c.last_name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
                        

-- 1
CREATE OR REPLACE VIEW v_book_catalog AS
SELECT b.id, b.title, b.price, a.name AS author
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

-- 2
DROP VIEW IF EXISTS v_expensive_books;

-- 3
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 4
SHOW CREATE VIEW v_order_summary;
                        

CREATE VIEW v_support_customers AS
SELECT id, first_name, last_name, email, phone, city
FROM customers
WHERE city IN ('Las Vegas', 'Henderson')
WITH CHECK OPTION;

-- Support team gets access to this view only:
-- GRANT SELECT ON bookstore.v_support_customers TO 'support'@'%';
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — CREATE VIEW Statement
• MySQL Docs — Updatable and Insertable Views
• MySQL Docs — View Restrictions

🚀 What's Next?

You can now query efficiently (indexes) and simplify complex queries (views). The last piece of Module 5 is designing the database itself. In Lesson 16: Database Design & Normalization, you'll learn to structure tables from scratch using normalization rules (1NF, 2NF, 3NF) — ensuring your data is organized, duplication-free, and maintainable.