CREATE VIEW

The CREATE VIEW statement creates a virtual table based on the result set of an SQL query. Views can be aliased and reused in other queries.

Syntax Summary

This code block is a quick reference with all the parameters that you can use with the CREATE VIEW statement.

See some practical examples.

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] name
AS querysql

Parameters

The CREATE VIEW statement accepts the following parameters.

You must provide the following:

name
query

Parameter Description

Parameter	Description
`IF NOT EXISTS`	Create a new view only if a view of the same name does not already exist (recommended).
`OR REPLACE`	Overwrite an existing view of the same name.
`name`	Name of the view. This name must be unique.
`query`	A `SELECT` statement. If the table in the `query` parameter does not exist at the time the view is created, this statement will fail.

IF NOT EXISTS

Create a new view only if a view of the same name does not already exist (recommended).

OR REPLACE

Overwrite an existing view of the same name.

name

Name of the view. This name must be unique.

query

A SELECT statement. If the table in the query parameter does not exist at the time the view is created, this statement will fail.

Dependency Management

If you change a mapping that a view depends on, you will invalidate that view. Also, if you drop a column from a mapping that the view v depends on, the command will succeed. However, subsequent queries such as SELECT * FROM v will fail because the column is missing.

The command to drop a column from the mapping succeeds because the query parameter in the view isn’t validated until the next time it is used.

Dropping or modifying a view that’s used in an active query does not affect the query; it will have effect only on newly-submitted queries.

Information Schema Views

The information_schema.views view provides a view into the engine metadata for the user. View columns are in information_schema.columns table.

Permissions

Enterprise

If security is enabled, you can grant or deny permission for certain clients to use this statement. See SQL Permissions.

Examples

This section lists some example SQL queries that show you how to use the CREATE VIEW statement.

CREATE VIEW highPerformingEmployees
AS
SELECT FirstName, LastName
FROM employees
WHERE performance > 70 ;sql