Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE VIEW customer_names AS
SELECT id, first_name || ' ' || last_name AS full_name, city
FROM customers;

Try it

SQL A view that hides a join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Views follow the data, and can be dropped ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 AS or 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 BY inside 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 BY or aggregates.