Skip to content
Daniël van Eeden edited this page Mar 13, 2024 · 8 revisions

SQL standard specifies the following CREATE VIEW syntax:

CREATE [RECURSIVE] VIEW

Dialects have considerable variation:

BigQuery:

CREATE [OR REPLACE] [MATERIALIZED] VIEW [IF NOT EXISTS]

DB2:

CREATE [OR REPLACE] VIEW

Hive:

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS]

MariaDB:

CREATE
  [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
  [SQL SECURITY { DEFINER | INVOKER }]
  VIEW [IF NOT EXISTS]

MySQL:

CREATE
  [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [DEFINER = user]
  [SQL SECURITY { DEFINER | INVOKER }]
  VIEW

N1QL:

No support for CREATE VIEW.

PL/SQL:

CREATE [OR REPLACE] [[NO] FORCE] [EDITIONING | EDITIONABLE [EDITIONING] | NONEDITIONABLE] VIEW

CREATE MATERIALIZED VIEW

PostgreSQL:

CREATE [OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE] VIEW

CREATE MATERIALIZED VIEW [IF NOT EXISTS]

Redshift:

CREATE [OR REPLACE | MATERIALIZED] VIEW

SingleStoreDB:

CREATE
  [DEFINER "=" {user | CURRENT_USER}]
  [SCHEMA_BINDING "=" {ON | OFF}]
  VIEW

Snowflake:

CREATE [OR REPLACE] [SECURE] [RECURSIVE] VIEW [IF NOT EXISTS]

Spark:

CREATE [OR REPLACE] [[GLOBAL] TEMPORARY] VIEW [IF NOT EXISTS]

SQLite:

CREATE [TEMPORARY | TEMP] VIEW [IF NOT EXISTS]

TiDB:

CREATE
  [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  [DEFINER = user]
  [SQL SECURITY { DEFINER | INVOKER }]
  VIEW

Transact-SQL:

CREATE [OR ALTER | MATERIALIZED] VIEW

Trino:

CREATE [OR REPLACE] [MATERIALIZED] VIEW
Clone this wiki locally