Chapter 6: Views
The customer worklist query that closed Chapter 5, using three joins, a status filter, and a sort, is exactly the kind of query that gets written once, works well, and then gets copy-pasted into five other reports over the following year. Each copy drifts slightly. One person adds a column, another fixes a typo in only their version, and six months later nobody is confident the five reports agree with each other. A view solves this by giving a query a name.
6.1 What a View Is
A view is a saved SELECT statement that behaves like a table. You query it the same way; SELECT, WHERE, ORDER BY all work against it exactly as they would against production.products, but underneath, every time you touch it, the database re-runs the original query.
CREATE VIEW sales.vw_customer_orders AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
o.order_id,
o.order_date,
o.order_status
FROM sales.customers AS c
INNER JOIN sales.orders AS o
ON c.customer_id = o.customer_id;
Nothing happens when this runs except that the query gets stored under the name vw_customer_orders. No rows are copied anywhere.
6.2 Querying a View
SELECT *
FROM sales.vw_customer_orders
WHERE order_status = 1
ORDER BY order_date DESC;
customer_id | first_name | last_name | order_id | order_date | order_status
-------------+------------+-----------+----------+------------+-------------
89 | Daniel | Cho | 1542 | 2018-12-19 | 1
207 | Priya | Nair | 1538 | 2018-12-14 | 1
This is the entire point. The two-table join only had to be written once, inside the view. Every query against vw_customer_orders from here on is a single-table SELECT as far as anyone writing it is concerned; WHERE, ORDER BY, even further joins to other tables, all stack on top of it normally.
SELECT
v.first_name,
v.last_name,
p.product_name
FROM sales.vw_customer_orders AS v
INNER JOIN sales.order_items AS oi ON v.order_id = oi.order_id
INNER JOIN production.products AS p ON oi.product_id = p.product_id
WHERE v.order_status = 1;
A view can be joined to other tables just like any real table can; the database expands the view's stored query first, then evaluates everything around it.
6.3 Altering and Dropping Views
-- SQL Server
CREATE OR ALTER VIEW sales.vw_customer_orders AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
o.order_id,
o.order_date,
o.order_status,
o.store_id
FROM sales.customers AS c
INNER JOIN sales.orders AS o
ON c.customer_id = o.customer_id;
-- PostgreSQL, Snowflake
CREATE OR REPLACE VIEW sales.vw_customer_orders AS
SELECT ...
Both forms redefine the view in place; anything querying it picks up the new definition immediately, with no need to touch the view's name or update any code that already references it. That's the second half of the maintenance benefit: fix the logic once, in one place, and every report built on it is correct from that point forward.
DROP VIEW sales.vw_customer_orders;
6.4 Updatable Views
A view built from a single table, with no joins, aggregates, or DISTINCT, is usually updatable; writes pass straight through to the underlying table.
CREATE VIEW production.vw_active_products AS
SELECT product_id, product_name, list_price
FROM production.products
WHERE list_price > 0;
UPDATE production.vw_active_products
SET list_price = 349.99
WHERE product_id = 1;
SELECT product_id, list_price FROM production.products WHERE product_id = 1;
product_id | list_price
-----------+-----------
1 | 349.99
The UPDATE ran against the view, but the change landed in production.products; the view has no storage of its own to update. vw_customer_orders from earlier in this chapter doesn't have this option: it pulls columns from two different tables, and the database has no way to know which underlying table a given write should target. Most databases simply refuse the write outright once a join, an aggregate, or DISTINCT enters the view's definition.
Interview Angle "Can you update data through a view?" The accurate answer is "sometimes"; a single-table view without aggregation or
DISTINCTis typically updatable, because every column maps unambiguously back to one row in one table. A view spanning multiple tables or computing aggregate values usually isn't, because the write would be ambiguous about where it should actually go.
6.5 Views Don't Store Data
A view holds no rows of its own; it's a name attached to a query, re-executed in full every time something touches it. Update the underlying customers or orders table and the view reflects that instantly, with nothing to refresh.
sales.vw_customer_orders
┌───────────────────────────┐
query ──────▶ │ stored SELECT statement │ ──────▶ result, computed fresh
└───────────────────────────┘ every single time
no rows stored here
This also means a view is not a performance shortcut. If the underlying join is slow, querying the view is exactly as slow; the database has no precomputed answer sitting around waiting. Some databases offer materialized views (Snowflake, PostgreSQL) or indexed views (SQL Server), which genuinely do store the result physically and need to be refreshed on a schedule or on data change. That's a real performance tool, but a different one, covered properly once indexing is introduced later in this book.
Interview Angle A common misconception worth correcting directly: "Does creating a view make my query faster?" No; a standard view is just a saved query, recomputed in full on every call. Speed comes from materialized or indexed views, which are a deliberate, separate decision with their own storage and refresh cost.
6.6 Why Use Views
Three reasons keep coming up in real schemas.
Hiding complexity. Anyone querying vw_customer_orders doesn't need to know it's a join at all; they get a clean, table-shaped object. The five-table chain behind a real analytics view can stay invisible to the analyst writing a report against it.
Security. A view can expose only the columns a particular audience should see.
CREATE VIEW sales.vw_customer_directory AS
SELECT customer_id, first_name, last_name, city, state
FROM sales.customers;
An external reporting team can be granted access to vw_customer_directory without ever being able to query email or phone directly from sales.customers; the view simply never exposes those columns, regardless of what permissions exist on the base table.
Consistency. If "active customer" or "completed order" has a specific business definition, encoding that definition once inside a view means every team building on top of it gets the same answer, instead of five slightly different WHERE clauses scattered across five different reports.
6.7 Putting It Together
The customer service worklist from the end of Chapter 5 gets rebuilt as a view, so the next person who needs "pending orders with customer and product detail" doesn't have to reassemble four tables from scratch.
CREATE VIEW sales.vw_pending_order_items AS
SELECT
c.first_name,
c.last_name,
o.order_id,
o.order_date,
p.product_name,
oi.quantity
FROM sales.orders AS o
INNER JOIN sales.customers AS c
ON o.customer_id = c.customer_id
INNER JOIN sales.order_items AS oi
ON o.order_id = oi.order_id
INNER JOIN production.products AS p
ON oi.product_id = p.product_id
WHERE o.order_status = 1;
Now any question about pending orders is a plain, single-table-looking query:
SELECT * FROM sales.vw_pending_order_items
WHERE order_date >= '2018-12-01'
ORDER BY order_date DESC;
first_name | last_name | order_id | order_date | product_name | quantity
-----------+-----------+----------+------------+-------------------------+----------
Daniel | Cho | 1542 | 2018-12-19 | Trek Fuel EX 8 29 | 1
Priya | Nair | 1538 | 2018-12-14 | Surly Wednesday Frameset| 2
Four joins and a filter, written once. Every future "pending orders" question becomes one short query against a name instead of a fresh four-table assembly.
Summary
A view is a saved SELECT statement that can be queried like a table: CREATE VIEW name AS followed by the query. It stores no data of its own; every query against it re-executes the underlying logic in full, which means a view is not a performance optimization on its own. CREATE OR ALTER (SQL Server) or CREATE OR REPLACE (PostgreSQL, Snowflake) redefines a view in place; DROP VIEW removes it. A view built from a single table without joins or aggregation is usually updatable; writes pass through to the base table, while views spanning multiple tables or computing aggregates generally aren't. Views earn their place for three reasons: hiding complex joins behind a simple name, restricting which columns an audience can see, and keeping a business definition consistent in one place instead of scattered across every report that needs it.
Exercises
6.1. Create a view called production.vw_product_catalog that joins products, categories, and brands to show product_name, category_name, brand_name, and list_price in one result.
6.2. Using the view from 5.1, write a query that returns only products under $500, sorted by price ascending.
6.3. Create a view sales.vw_customer_contact that exposes only customer_id, first_name, last_name, and city from sales.customers, explicitly excluding email and phone. Explain in one sentence what real-world problem this view solves.
6.4. Explain why the following view cannot be used in a simple UPDATE statement, and identify exactly which part of its definition causes the restriction:
CREATE VIEW sales.vw_order_summary AS
SELECT o.order_id, c.first_name, c.last_name, o.order_status
FROM sales.orders AS o
INNER JOIN sales.customers AS c ON o.customer_id = c.customer_id;
6.5. A teammate says, "I made this report faster by turning the query into a view." Explain whether this claim is accurate, and what would actually need to happen for a view to improve performance.
6.6. Think About It: Two different teams each maintain their own version of a "completed orders" query, written independently, six months apart. One filters order_status = 4; the other accidentally filters order_status = 3 after misreading the status codes from Chapter 4. What specific failure does a shared view prevent here that code review and documentation alone do not?