SQLite: VIEW

This SQLite post explains how to create, update, and drop VIEWS in SQLite with syntax and examples.

What is a VIEW in SQLite?

In SQLite, a VIEW is not a physical table, but rather, it is in essence a virtual table created by a query joining one or more tables.

Create VIEW

Syntax

The syntax for the CREATE VIEW statement in SQLite is:

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

view_name

The name of the VIEW that you wish to create in SQLite.

WHERE conditions

Optional. The conditions that must be met for the records to be included in the VIEW.

Example

Here is an example of how to use the CREATE VIEW statement to create a view in SQLite:

CREATE VIEW active_employees AS
  SELECT employee_id, last_name, first_name
  FROM employees
  WHERE hire_date IS NOT NULL;

This CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the SQLite VIEW as follows:

SELECT *
FROM active_employees;

Drop VIEW

Once a VIEW has been created in SQLite, you can drop it with the DROP VIEW statement.

Syntax

The syntax for the DROP VIEW statement in SQLite is:

DROP VIEW [IF EXISTS] view_name;

view_name

The name of the view that you wish to drop.

IF EXISTS

Optional. If you do not specify this clause and the VIEW does not exist, the DROP VIEW statement will return an error.

Example

Here is an example of how to use the DROP VIEW statement in SQLite:

DROP VIEW active_employees;

This DROP VIEW example would drop/delete the SQLite VIEW called active_employees.