​

CREATE VIEW

​

Syntax

CREATE [TEMPORARY] VIEW [IF NOT EXISTS] [schema-name.]view-name
    AS select-statement;

​

Description

​

Parameters

​

Behavior

• Views are read-only. INSERT, UPDATE, and DELETE on a view are not supported unless an INSTEAD OF trigger is defined on the view.
• The SELECT statement is stored in the sqlite_schema table and parsed each time the view is referenced.
• If a table referenced by the view is dropped or altered in an incompatible way, queries against the view produce an error.

​

Examples

​

Simplify a Common Query

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Use the view like a table
SELECT * FROM active_users WHERE name LIKE 'A%';

​

Aggregate View

CREATE VIEW department_stats AS
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

SELECT * FROM department_stats ORDER BY avg_salary DESC;

​

Temporary View for a Session

CREATE TEMP VIEW my_tasks AS
SELECT id, title, due_date
FROM tasks
WHERE assignee = 'alice';

​

See Also

• CREATE MATERIALIZED VIEW for views that store precomputed results on disk
• DROP VIEW for removing views
• SELECT for the query syntax used in view definitions
• CREATE TRIGGER for INSTEAD OF triggers that make views writable