Views

A view is a convenient way of packaging a query into an object that can itself be used in the FROM clause of a SELECT statement.

Creating and Dropping Views

The syntax for CREATE VIEW is shown next.

 CREATE [TEMP | TEMPORARY] VIEW view-name AS select-statement 

The select-statement can be as simple or as complex as necessary; it could return the subset of a single table based on a conditional WHERE clause, or join many tables together to form a single object that can be more easily referenced in SQL.

To drop a view, simply use the DROP VIEW statement with the view-name given when it was created.

A view is not a table. You cannot perform an UPDATE, INSERT, COPY, or DELETE on a view, but if the data in one of the source tables changes, those changes are reflected instantly in the view.

Using Views

The following example shows a view based on the demo database tables employees and employee_rates using a query that returns the current rate of pay for each employee.

 sqlite> CREATE VIEW current_pay AS    ...> SELECT e.*, er.rate    ...> FROM employees e, employee_rates er    ...> WHERE e.id = er.employee_id    ...> AND er.end_date IS NULL; 

We can then query the new view directly, even adding a new condition in the process:

 sqlite> SELECT * FROM current_pay    ...> WHERE sex = 'M'; id    first_name  last_name   sex  email                     rate ----  ----------  ----------  ---  ------------------------  ------ 101   Alex        Gladstone   M    [e-mail]        30.00 103   Colin       Aynsley     M    [e-mail]       25.00 

The column names in a view are the column names from the table. Where an expression is used, SQLite will faithfully reproduce the expression as the column heading.

 sqlite> CREATE VIEW veg_upper AS    ...> SELECT upper(name), upper(color)    ...> FROM vegetables; sqlite> SELECT * FROM veg_upper LIMIT 1; upper(name)|upper(color) CARROT|GREEN 

However, the column in the view cannot actually be called upper(name). As shown in the following example, SQLite will attempt to evaluate the upper() function on the nonexistent name column.

 sqlite> SELECT upper(name) from veg_upper; SQL error: no such column: name 

Column aliases can be used to give an explicit name to a column so that they can be referenced within a subsequent query.

 sqlite> CREATE VIEW veg_upper AS    ...> SELECT upper(name) AS uppername, upper(color) AS uppercolor    ...> FROM vegetables; sqlite> SELECT * FROM veg_upper    ...> WHERE uppercolor = 'ORANGE'; uppername|uppercolor CARROT|ORANGE PUMPKIN|ORANGE 

Note

When a view includes two columns with the same namewhether it is the same column selected twice from one table, or once each from two tables that happen to share a column nameSQLite will modify the column names in the view unless aliases are used. A duplicate column will be suffixed with _1 the first time it appears, _2 the second time, and so on.

SQLite does not validate the select-statement SQL in CREATE VIEW. You will only know if there is an error in the SELECT when you come to query the new view. The view's SELECT statement is effectively substituted into the query at the point where view-name appears, so the errors displayed may not appear to reflect the query you typed.

The following example creates a view with a deliberate errorthere is no column entitled shape in vegetablesand shows that the error is not detected until you query the view.

 sqlite> CREATE VIEW veg_error AS    ...> SELECT shape FROM vegetables; sqlite> SELECT * from veg_error; SQL error: no such column: shape