SQL views: stored queries as virtual tables

In short: A view is a named, stored query that can be used like a table; it simplifies complex queries and can restrict access to columns.

In more detail: Instead of writing a long query with several joins again and again, you store it once as a view (CREATE VIEW). Afterwards you query SELECT * FROM view_name. A view normally holds no data of its own but runs the query on every call.

In Depth

Benefits

• Simplification: complex joins and calculations in one place.
• Access control: users see only selected columns/rows (for example without the salary column); permissions are granted on the view.
• Stable interface: the table structure may change while the view keeps the result the same for applications.
• Readability of reports.

Materialised views

A materialised view (CREATE MATERIALIZED VIEW) stores the result physically. Queries become fast, but the data is only as current as the last REFRESH MATERIALIZED VIEW. Suited for expensive analyses (statistics, dashboards) that need not be accurate to the second. In PostgreSQL you can refresh with CONCURRENTLY without locking readers (requires a unique index).

Limits

• Ordinary views are not faster than the underlying query; the planner expands them into the query.
• Writing through views works only for simple views (one table, no aggregation) or with INSTEAD OF triggers.
• Nested views quickly become confusing and slow.
• Changes to base tables can invalidate views; document dependencies.

Exam view

View = virtual table in the external schema of the three-schema architecture. Benefits: data independence and data protection. Counterpart: materialised view as a cached result.

See also: Joins, Query planner and EXPLAIN, Subqueries and CTEs