SQLite Views

SQLite views are logical representations of tables that can be used to query data in the database. Views can be created from one or more tables, and they can be used to query data in the database just like regular tables.

Views are often used to simplify complex queries, or to hide sensitive data from users who should not have access to it.

Types of Views

There are two types of views in SQLite:

Temporary views – exist only for the duration of the session.
Persistent views – exist until they are dropped.

Creating a Temporary View

To create a temporary view, you use the CREATE TEMPORARY VIEW statement. The following is the syntax for creating a temporary view:

CREATE TEMPORARY VIEW view_name AS
SELECT column1, column2, ...
FROM table_name;

For example, to create a temporary view that contains the data from the customers and orders tables, you use the following statement:

CREATE TEMPORARY VIEW customer_orders AS 
SELECT c.name, o.order_id, o.amount
FROM customers c, orders o
WHERE c.id=o.customer_id;

Creating a View

To create a view, also called Persistent View, you use the CREATE VIEW statement without the TEMPORARY keyword. The following is the syntax for creating a view:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name;

For example, to create a view that contains the data from the books table, you use the following statement:

CREATE VIEW books_view AS 
SELECT * FROM books 
WHERE description IS NOT NULL;

Dropping a View

To drop a view, you use the DROP VIEW statement. The following is the syntax for dropping a view:

DROP VIEW view_name;

For example, to drop the customer_orders view, you use the following statement:

DROP VIEW customer_orders;