SQL Reference ยท Data Definition
SQL CREATE VIEW statement
Reference for SQL CREATE VIEW: save a query under a name, query it like a table, drop or replace it, and how materialized and updatable views differ.
A view is a saved SELECT query with a name. You query it like a table, but it stores no rows of its own: every time you read the view, the database runs the underlying query against the current data.
Views hide complexity (joins, calculations), give different users a restricted slice of a table and keep an interface stable when the tables underneath change.
CREATE VIEW customer_names AS SELECT id, first_name || ' ' || last_name AS full_name, city FROM customers;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
CREATE [TEMP] VIEW [IF NOT EXISTS] view_name [(column_name, ...)] AS select_statement; DROP VIEW [IF EXISTS] view_name;
| Part | Description |
|---|---|
view_name |
The name; shares the namespace with tables. |
(column_name, ...) |
Optional list of output column names, overriding the query's. |
select_statement |
The query. Alias computed columns so the view has usable names. |
Result: the view appears in the schema; reading it executes the query.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Read-only views (no INSERT/UPDATE/DELETE unless you add INSTEAD OF triggers). No CREATE OR REPLACE: drop and recreate. No materialized views. |
| PostgreSQL | CREATE OR REPLACE VIEW; CREATE MATERIALIZED VIEW stores results, refreshed with REFRESH MATERIALIZED VIEW. Simple views are automatically updatable. |
| MySQL | CREATE OR REPLACE VIEW; simple single-table views are updatable; WITH CHECK OPTION is supported. No materialized views. |
| SQL Server | CREATE OR ALTER VIEW (2016 SP1+); indexed views act as materialized views. |
| Oracle | CREATE OR REPLACE VIEW; CREATE MATERIALIZED VIEW with refresh options; WITH READ ONLY is available. |
-- PostgreSQL CREATE MATERIALIZED VIEW monthly_sales AS SELECT substr(ordered_at, 1, 7) AS month, COUNT(*) AS orders FROM orders GROUP BY substr(ordered_at, 1, 7); REFRESH MATERIALIZED VIEW monthly_sales;
Usage notes
- Not a copy. A view is always current; for a stored snapshot use
CREATE TABLE ASor a materialized view. - No speed-up by itself. A plain view is just inlined into the outer query. Indexes on the underlying tables are what matter.
SELECT *freezes columns. In some databases the column list is fixed at creation, so later added columns do not appear. Name columns explicitly.ORDER BYinside a view is not guaranteed to order the final result; sort in the outer query.- Security: views limit which columns and rows a user sees, but check your database's privilege rules for the underlying tables.
- Updatable views generally must map each row to one base-table row: no
DISTINCT,GROUP BYor aggregates.
Related
CREATE TABLE AS,CREATE TRIGGER,DROP TABLE,WITH(CTE)- Lessons: Views, CTEs