SQL-Engineering-Handbook

01 — Introduction to Views

Module: 14 — Views Previous: README · Next: 02 — Creating Views

A view is a stored query, not stored data

Learning Objectives

Concept Overview

A View is a named, stored SELECT statement that behaves like a table for querying purposes. It has a schema (column names and types, inherited from its defining query) and can be queried, joined, and filtered like a table — but it stores no rows of its own. Every time you query a View, MySQL re-runs (or expands) the underlying SELECT against the current state of the base tables.

CREATE VIEW vw_name AS
SELECT ...
FROM base_table;

SELECT * FROM vw_name;   -- always reflects current base_table data

Why This Exists

SQL databases are built around normalized schemas — data is split across many tables to avoid duplication and preserve integrity. That’s excellent for write correctness and terrible for readability. A single “what were our top customers by revenue last quarter” question might require joining five tables with several CASE expressions and NULL handling baked in. Without a View, every analyst who needs that answer re-derives that join from scratch, and re-derivations drift.

A View freezes that derivation into a single named object. The complexity is paid once, by whoever writes the View; every consumer after that just does SELECT * FROM vw_top_customers.

Business Context

Consider a Sales analytics team. The raw schema:

sales_customers   (customer_id, customer_name, region, signup_date)
sales_orders      (order_id, customer_id, order_date, status)
sales_order_items (order_item_id, order_id, product_id, quantity, unit_price)

“Revenue” is not a column anywhere — it’s quantity * unit_price, summed across sales_order_items, joined back to orders and customers, filtered to status = 'COMPLETED' because cancelled and refunded orders shouldn’t count. Every analyst on the team needs to know all four of those rules just to answer “what’s our revenue by customer” correctly. A View encodes the rules once.

Where Companies Use It

Real Business Example

CREATE VIEW vw_completed_order_revenue AS
SELECT
    c.customer_id,
    c.customer_name,
    c.region,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM sales_customers AS c
INNER JOIN sales_orders AS o
    ON o.customer_id = c.customer_id
INNER JOIN sales_order_items AS oi
    ON oi.order_id = o.order_id
WHERE o.status = 'COMPLETED'
GROUP BY c.customer_id, c.customer_name, c.region;

Any analyst can now write SELECT * FROM vw_completed_order_revenue WHERE region = 'APAC'; without knowing the join logic exists.

Syntax

CREATE [OR REPLACE] VIEW view_name [(column_list)] AS
SELECT ...
[WITH [CASCADED | LOCAL] CHECK OPTION];

Minimal form:

CREATE VIEW view_name AS SELECT ... ;

Visual Explanation

┌─────────────────────┐
│   Query issued by    │
│  analyst / BI tool    │
│ SELECT * FROM vw_x    │
└──────────┬───────────┘
           │
           ▼
┌───────────────────────────┐
│   View definition (SQL)    │  ← stored in information_schema,
│   SELECT ... FROM base_t   │     NOT as data
└──────────┬─────────────────┘
           │  expanded/merged by optimizer
           ▼
┌───────────────────────────┐
│      Base table(s)         │  ← actual rows live here
│  sales_orders, etc.         │
└─────────────────────────────┘

Step-by-Step Walkthrough

  1. MySQL parses CREATE VIEW and stores the definition text (the SELECT) in information_schema.VIEWS, not any data.
  2. When a query references the View, the optimizer either merges the View’s SELECT into the outer query as if you’d written it inline (ALGORITHM = MERGE), or materializes it into an internal temporary table first (ALGORITHM = TEMPTABLE).
  3. The base tables are read fresh on every execution — a View is never stale, because it never stored data to begin with.

Engineering Notes

Production Considerations

Views become part of your schema’s public contract the moment another team or tool starts querying them. Dropping or restructuring a View without checking information_schema for dependents is a common cause of production incidents — covered in 06_VIEW_LIMITATIONS.md.

Performance Notes

A View adds zero storage overhead and zero write overhead (nothing to keep in sync). Its entire cost is at read time, and that cost is identical to running the underlying query directly — the View is not “compiled” or cached in MySQL.

Edge Cases

Best Practices

Common Mistakes

Mistake Consequence
Assuming a View caches results Stale-data bugs never happen, but performance assumptions are wrong
Using SELECT * Silent column drift when base table changes
Treating a View as a performance tool Slow base query → slow View, always

Interview Questions

  1. “What is a View, and what does it store?” — Expected answer: it stores the query definition, not data; it’s metadata.
  2. “Does querying a View always return live data?” — Yes, because the underlying query re-executes every time (unless refreshed via materialized-view-style patterns, which MySQL doesn’t natively support — see Module 08).
  3. “Why would you use a View instead of just writing the join every time?” — Reusability, consistency of business logic, and access control.

Summary

A View is a stored SELECT statement with a name, not a copy of data. It exists to centralize business logic, provide a stable query interface, and enable access control — at zero storage cost and read-time cost identical to the underlying query.

Practice Challenges

  1. Without running any SQL, predict what happens if you DROP a column from sales_orders that a View references with SELECT *. Then verify.
  2. Write (on paper) a View definition for “active SaaS subscriptions” using the business rule: status IN ('TRIALING','ACTIVE','GRACE_PERIOD').

Further Reading

This site is open source. Improve this page.