Views

A CTE, covered earlier in this module, is a named query that only exists for one statement. A view is the permanent version of that idea: a query saved in the database under a name, that can be queried again and again afterward, in any number of separate statements, exactly like a regular table.

Learning Objectives

• Create a view with CREATE VIEW, and query it like a table.
• Explain what makes a view different from an actual table.
• Know when a view can be updated directly, and when it can't.

Creating and Using a View

Once created, high_earners behaves like a table in every query that follows, you can SELECT from it, filter it further with WHERE, join it to other tables. Under the hood, there's no actual copy of the data, every time the view is queried, the database re-runs the underlying SELECT fresh, so the view's results are always current.

CREATE VIEW high_earners AS
SELECT first_name, department, salary
FROM employees
WHERE salary > 90000;

SELECT * FROM high_earners
WHERE department = 'Engineering';

Why Use a View

• Hide a complicated join or calculation behind a simple name, so other queries (or less experienced teammates) don't need to repeat it.
• Give a restricted or simplified slice of a table to certain users, for example a view that leaves out a salary column entirely.
• Keep a frequently-used query consistent and defined in exactly one place, instead of copy-pasted across many application queries.

Updatable Views

A simple view, built from a single table with no aggregation, grouping, or DISTINCT, can usually be updated directly, an UPDATE or INSERT against the view actually modifies the underlying table. A complex view, one with a JOIN, GROUP BY, or an aggregate function, generally can't be, there's no unambiguous way to translate a change back to the original rows.

UPDATE high_earners
SET department = 'Research'
WHERE first_name = 'Ada';

-- Works, because high_earners is a simple, single-table view,
-- this actually updates the underlying employees table.

Materialized Views

A regular view re-runs its query every single time it's queried, which can be slow if the underlying query is expensive. A materialized view instead actually stores the computed result physically, like a real table, and has to be explicitly refreshed to pick up new data. It trades perfect freshness for speed, useful for an expensive report or dashboard query that doesn't need to be up-to-the-second.

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use a view to hide a complex, frequently-repeated query behind a simple, readable name.
• Add WITH CHECK OPTION on an updatable view that's meant to enforce a filtering rule, so updates through it can't silently violate that rule.
• Reach for a materialized view (or a manually refreshed table, on MySQL/SQLite) only when a regular view's query is genuinely too slow to re-run on every read.
• Keep view nesting shallow, one or two levels at most, for the sake of anyone debugging a slow or confusing query later.

Interview Questions

Summary

This lesson covered creating and querying a view like a regular table, why a simple single-table view is usually updatable while a complex one generally isn't, WITH CHECK OPTION for enforcing a view's filter on writes, and materialized views as a way to trade real-time freshness for speed.

That completes Module 4, Advanced Queries: window functions, CTEs, set operations, and views, rounding out the intermediate-to-advanced SQL a working developer uses day to day.

What's Next?

More modules are planned for this course, check the course page for progress as new lessons go live.