Subset of SELECT syntax valid in view definitions

Most SELECT statement syntax is supported in view definitions, with certain exceptions.

  • You cannot create a view on a temporary table. The FROM clause of the SELECT clause of the view definition cannot include the name of a temporary table.

  • Table objects referenced by the SELECT clause in CREATE VIEW statements can be permanent database tables, views, or derived tables. The query can reference a single table object, or can join two or more. These can be in the current database, in other databases of the local database server, or in databases of remote server instances. The SELECT statement can define derived tables in the FROM clause, using uncorrelated or correlated table references. These derived table definitions can include the LATERAL keyword, and can include lateral table and column references.

  • Table objects referenced by the SELECT clause in CREATE VIEW statements can be permanent database tables, views, or derived tables. The query can reference a single table object, or can join two or more. These can be in the current database, in other databases of the local database server, or in databases of remote server instances. The SELECT statement can define derived tables in the FROM clause, using uncorrelated or correlated table references. These derived table definitions can include the LATERAL keyword, and can include lateral table and column references.

  • If Select privileges are revoked from a user for a table that is referenced in the SELECT statement defining a view that the same user owns, then that view is automatically dropped by the database server, unless the view also includes columns from tables in another database.

  • You cannot create a view on a table that is part of a typed-table hierarchy, if that table resides in a database of a different database server instance.

  • Do not use display labels in the Select list of the Projection clause in a view definition. Display labels in the Projection clause are interpreted as column names.

  • Hardcoded values should not be used in a view definition, but only in the WHERE clause of subsequent SELECT statements that query the view. If the values are not hardcoded in the view, the query optimizer can then always exclude those literal values and can complete the query in less time. But if the same values are hardcoded in the view, the query optimizer still must evaluate each literal value.

  • The SELECT clause in the CREATE VIEW statement cannot include the SKIP, FIRST, or LIMIT keywords, or the INTO TEMP clause.

For complete information about SELECT statement syntax and usage, see SELECT statement.