Views

A view is a saved, named SELECT query that the database lets you treat like a table. It does not store its own copy of data (with one exception covered on the next page) — instead, every time you query a view, the database runs the underlying SELECT statement behind the scenes and hands you the result. Views are one of the simplest ways to make a complex schema easier to work with, without changing the schema itself.

Creating a view

A view is created with CREATE VIEW, followed by a name and the query it wraps. Almost any SELECT statement can become a view, including ones with joins, aggregations, and filters.

Creating a view over a join

SQL

CREATE VIEW active_customer_orders AS
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.status = 'active';

Once created, the view can be queried exactly like a regular table — with WHERE, ORDER BY, JOINs against other tables, and so on. The database expands the view's definition into your query automatically.

Querying a view like a table

SQL

SELECT *
FROM active_customer_orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;

Why use a view?

Views solve two very different problems that happen to share the same mechanism.

Simplifying repeated complexity — if several reports all need the same three-table join with the same filters, wrapping it in a view means writing that join once instead of copy-pasting it everywhere it is needed.
Restricting or reshaping access — a view can expose only a subset of columns or rows from an underlying table, which is useful for hiding sensitive columns (like salary or a password hash) from users or applications that should not see them, without duplicating the table or managing column-level permissions.

A view that hides sensitive columns

SQL

CREATE VIEW employee_directory AS
SELECT employee_id, full_name, department, email
FROM employees;
-- salary and other sensitive columns are simply never exposed

Dropping a view

A view is removed with DROP VIEW. Dropping a view never deletes any data, since a view never owned any data to begin with — it only removes the saved query definition.

Removing a view

SQL

DROP VIEW active_customer_orders;

DROP VIEW

A view is not a copy of the data

By default, a view stores no rows at all — only the SELECT statement that defines it. Every query against the view re-runs that SELECT against the current, live data in the underlying tables, so a view always reflects the latest state of the base tables. This is different from a materialized view, which does physically store its result set and must be refreshed to catch up with changes — that trade-off is covered on the next page.

Views can be layered

A view can be built on top of another view, which is convenient for building up complexity in stages. Be cautious with deeply nested views, though — several layers of views stacked on top of each other can make it hard to reason about what a query is actually doing, and can hurt performance if the database cannot optimize across all the layers effectively.

A view is a saved, named SELECT query that can be queried like a table.
Views simplify repeated complex queries and can restrict which columns or rows are visible.
By default a view stores no data — it re-runs its underlying query every time it is used.
DROP VIEW removes the view definition without touching any underlying data.