CREATE VIEW

DDL
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

Filter by Database
SQL CREATE VIEW Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all Standard views supported. No materialized views.
PostgreSQL Native all Standard views + MATERIALIZED VIEW (since 9.3) with REFRESH MATERIALIZED VIEW [CONCURRENTLY] (since 9.4).
SQL Server Native all Standard views + indexed views (CREATE UNIQUE CLUSTERED INDEX on a view materializes it, similar to a materialized view).
Oracle Native all Standard views + MATERIALIZED VIEW (predates PostgreSQL's). Supports ON COMMIT REFRESH and ON DEMAND REFRESH.
SQLite Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

Standard views supported. No materialized views.

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

PostgreSQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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

CREATE VIEW v AS SELECT col FROM tbl;