CREATE VIEW ​

Views allow the user to create a simplified version of a given SQL statement.

It is a logical table based on one table. A view contains no data in itself.

For example, you can:

  • change the names of some selected columns.
  • change the order of the columns.
  • filter the result of a select SQL statement on a table.

Syntax ​

text
CREATE VIEW <ViewName> [(new_name1, new_name2)]
AS
	select_statement

<ViewName> ​

Name of the given view.

The view is created in the same schema of the underlying table.

Allowed sql_statements ​

Using a JOIN clause with other tables is not allowed. If you use a JOIN clause, the FROM clause only specifies one table at a time.

List of fields ​

Create a view that will display only selected fields.

sql
CREATE VIEW ViewName 
AS
	SELECT column1, column2 from data

All fields ​

sql
CREATE VIEW ViewName
AS
	SELECT * from data

Rename columns ​

When defining the field names of the view, you can rename fields of the original table:

text
CREATE VIEW ViewName(column1_new_name, column2_new_name)
AS
	SELECT column1, column2 from DATA

Or during the select statement:

text
CREATE VIEW ViewName
AS
	SELECT column1 as NewName1, column2 as NewName2 from DATA

Filter on a given field ​

sql
CREATE VIEW ViewName
AS
	SELECT * FROM mytable WHERE [mytable.]column1 = 1

Order the results ​

text
CREATE VIEW new_name_for_old_data_table 
AS 
	SELECT * FROM mytable 
	ORDER BY [mytable.]column1 desc

Limit the number of results ​

sql
CREATE VIEW limited_table 
AS
	SELECT * FROM mytable 
	LIMIT 1000