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.
CREATE VIEW <ViewName> [(new_name1, new_name2)] AS select_statement
Name of the given view.
The view is created in the same schema of the underlying table.
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.
CREATE VIEW ViewName AS SELECT column1, column2 from data
CREATE VIEW ViewName AS SELECT * from data
When defining the field names of the view, you can rename fields of the original table:
CREATE VIEW ViewName(column1_new_name, column2_new_name) AS SELECT column1, column2 from DATA
Or during the select statement:
CREATE VIEW ViewName AS SELECT column1 as NewName1, column2 as NewName2 from DATA
Filter on a given field
CREATE VIEW ViewName AS SELECT * FROM mytable WHERE [mytable.]column1 = 1
Order the results
CREATE VIEW new_name_for_old_data_table AS SELECT * FROM mytable ORDER BY [mytable.]column1 desc
Limit the number of results
CREATE VIEW limited_table AS SELECT * FROM mytable LIMIT 1000