Views
SQL · Views & Triggers
Theory
A view is a saved SELECT query with a name. CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 80000 stores only the query text, not the rows. Whenever someone runs SELECT * FROM high_earners, the database runs the stored query against the current tables, so the view always shows up-to-date data, and you can filter, join and group it just like a table.
Views hide complex joins behind a simple name, give every team the same definition of a metric, and act as a security layer: users can be granted access to a view that exposes only certain columns or rows. Simple single-table views can often be updated through, but views with joins, GROUP BY or DISTINCT generally cannot. A materialized view is different: it stores the result physically and must be refreshed, trading freshness for speed.
Why we use it
The same joins and filters get rewritten in many reports, often slightly differently, which leads to mismatched numbers. A view defines the logic once, keeps it consistent and lets users query it without knowing the underlying tables.
When to use it
Use views to simplify repeated queries, share business logic and restrict what users can see. When the underlying query is slow and is read very often, consider a materialized view or a summary table instead.
How it works
- CREATE VIEW high_earners AS SELECT ...
- Database stores the query text, not the rows
- User runs SELECT * FROM high_earners
- Engine substitutes the view's query into the user's query
- Runs it against the current base tables
- Returns fresh rows, like a table
Syntax
CREATE VIEW view_name AS
SELECT columns FROM table WHERE condition;Example
CREATE VIEW high_earners AS
SELECT name, salary FROM employees WHERE salary > 80000;Key points
- Simplifies complex or frequently reused queries
- Does not store data itself, just the query definition
- Always reflects the current data in the base tables
- Can restrict access to chosen columns or rows
- Materialized views store results and need refreshing