DDL

CREATE VIEW

Q: What's the difference between a view and a materialized view?

VIEW re-runs the query each time you select from it — always fresh, no storage. MATERIALIZED VIEW stores the result physically — fast to read, takes disk, becomes stale until refreshed. Use views for naming abstractions and access control; use materialized views (or summary tables) for expensive aggregations queried far more often than the data changes.

Q: How do I refresh a materialized view?

Postgres: REFRESH MATERIALIZED VIEW name (with CONCURRENTLY for non-blocking). Snowflake: auto-refresh. Oracle: EXEC DBMS_MVIEW.REFRESH('name') or scheduled refresh. BigQuery: incremental refresh based on the source data. MySQL has no materialized views — emulate with a real table populated by INSERT ... SELECT on a schedule.

Save a query as a named, queryable object. A view stores no data; it stores the definition. Each time you select from it, the engine substitutes the underlying query and runs it. The right tool for naming a complicated join, hiding sensitive columns, or giving downstream consumers a stable contract while you reshape the tables underneath.

Syntax #

sql

CREATE [OR REPLACE] VIEW view_name [(column_alias, ...)] AS
SELECT ...;

-- Drop a view.
DROP VIEW [IF EXISTS] view_name;

-- Materialized view: results are stored, refreshed on demand.
CREATE MATERIALIZED VIEW view_name AS SELECT ...;
REFRESH MATERIALIZED VIEW view_name;

Example #

Loading SQL editor...

View vs materialized view #

VIEW: re-runs the query each time you select from it. Always fresh, no storage cost, can be slow on heavy aggregations. Best for naming abstractions and access control.
MATERIALIZED VIEW: stores the result physically. Fast to read, takes disk, becomes stale until refreshed. Best for expensive aggregations queried far more often than the underlying data changes.

In practice, analytics teams often skip materialized views and build summary tables via dbt or scheduled jobs: easier to test, easier to reason about, and works on every engine.

Dialect notes #

OR REPLACE: Postgres, DuckDB, MySQL, Snowflake, Oracle. SQL Server uses CREATE OR ALTER VIEW (2016+). SQLite has no OR REPLACE, so drop and recreate.
Materialized views: native in Postgres (manual refresh), Oracle (manual or auto), Snowflake (auto), BigQuery (incremental, with constraints). MySQL and SQLite have no materialized views — emulate with a table + scheduled INSERT INTO ... SELECT.
Updatable views: simple single-table views are updatable in Postgres, MySQL, SQL Server, Oracle. Views with joins, aggregates, or DISTINCT are read-only.
Schema dependency: most engines lock the view definition to the underlying columns. Renaming an underlying column breaks the view; you'll get an error on the next select. OR REPLACE is your friend during migrations.

FAQ #

What's the difference between a view and a materialized view?

VIEW re-runs the query each time you select from it — always fresh, no storage. MATERIALIZED VIEW stores the result physically — fast to read, takes disk, becomes stale until refreshed. Use views for naming abstractions and access control; use materialized views (or summary tables) for expensive aggregations queried far more often than the data changes.

Can I update through a view?

Simple single-table views are usually updatable on Postgres, MySQL, SQL Server, Oracle. Views with joins, aggregates, or DISTINCT are read-only. The exact rules vary by engine; test before relying on it. If you need write semantics, an INSTEAD OF trigger can make complex views updatable.

Are views slower than queries?

No. Modern optimizers inline the view definition into the surrounding query, producing the same plan as the equivalent inline query. The "view tax" myth comes from older engines. Check EXPLAIN to confirm; if a view shows up as a planning bottleneck, the issue is usually the underlying query, not the view layer.

How do I refresh a materialized view?

Postgres: REFRESH MATERIALIZED VIEW name (with CONCURRENTLY for non-blocking). Snowflake: auto-refresh. Oracle: EXEC DBMS_MVIEW.REFRESH('name') or scheduled refresh. BigQuery: incremental refresh based on the source data. MySQL has no materialized views — emulate with a real table populated by INSERT ... SELECT on a schedule.

View vs CTE vs subquery: which to use?

View: when the named result is reused across many queries. CTE: when the named result is used once, within a single query. Subquery: same as CTE but inline (less readable past 2 levels). For lasting abstractions used by analysts and applications, views (or dbt models) are the right tool.

Can views reference other views?

Yes — chained views work on every engine. The downside: debugging a 5-level deep view chain when something's wrong is painful. Most teams keep view chains shallow (1-2 levels) and use dbt models for deeper transformations.

Tip

Use views as a contract layer. Application code and BI tools select from monthly_sales; the table layout underneath can change as long as the view's column list and meaning stay constant. This insulates downstream consumers from refactoring upstream. The discipline: never let downstream code reference base tables directly when a view exists for that purpose.