CREATE VIEWDDL
Buy Me A Coffee

Define a named virtual table based on a SELECT query.

Saved queries that behave like virtual tables, encapsulating complex logic and simplifying access control. Views can be nested (view on view), and some support updatability. Security views are powerful for column-level access control.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Standard views supported. No materialized views.
PostgreSQL ✓ Supported all Standard views + MATERIALIZED VIEW (since 9.3) with REFRESH MATERIALIZED VIEW [CONCURRENTLY] (since 9.4).
SQL Server ✓ Supported all Standard views + indexed views (CREATE UNIQUE CLUSTERED INDEX on a view materializes it, similar to a materialized view).
Oracle ✓ Supported all Standard views + MATERIALIZED VIEW (predates PostgreSQL's). Supports ON COMMIT REFRESH and ON DEMAND REFRESH.
SQLite ✓ Supported all Views are read-only. INSERT/UPDATE/DELETE through a view requires an INSTEAD OF trigger.

Details

PostgreSQL and Oracle support true MATERIALIZED VIEWs (pre-computed, stored on disk). SQLite views are read-only. SQL Server achieves materialization via indexed views.

Standard Syntax

CREATE VIEW view_name AS SELECT col1, col2 FROM table_name WHERE condition;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Standard views supported. No materialized views.

CREATE VIEW v AS SELECT col FROM tbl WHERE active = 1;

PostgreSQL

Standard views + MATERIALIZED VIEW (since 9.3) with REFRESH MATERIALIZED VIEW [CONCURRENTLY] (since 9.4).

CREATE VIEW v AS SELECT col FROM tbl; CREATE MATERIALIZED VIEW mv AS SELECT col FROM tbl; REFRESH MATERIALIZED VIEW CONCURRENTLY mv;

SQL Server

Standard views + indexed views (CREATE UNIQUE CLUSTERED INDEX on a view materializes it, similar to a materialized view).

CREATE VIEW v AS SELECT col FROM tbl; -- Materialize it: CREATE UNIQUE CLUSTERED INDEX idx ON v (col);

Oracle

Standard views + MATERIALIZED VIEW (predates PostgreSQL's). Supports ON COMMIT REFRESH and ON DEMAND REFRESH.

CREATE VIEW v AS SELECT col FROM tbl; CREATE MATERIALIZED VIEW mv REFRESH ON DEMAND AS SELECT col FROM tbl;

SQLite

Views are read-only. INSERT/UPDATE/DELETE through a view requires an INSTEAD OF trigger.

CREATE VIEW v AS SELECT col FROM tbl;