VIEW

Creates a view.

CREATE VIEW view_name AS
SELECT ...;

Creates or replaces an existing view (MySQL, PostgreSQL).

CREATE OR REPLACE VIEW view_name AS
SELECT ...;

Drops a view.

DROP VIEW view_name;

Drops a view only if it exists.

DROP VIEW IF EXISTS view_name;

The following examples use the members table and orders table.

Creates a view that joins active members with their order information.

sample_view.sql

CREATE OR REPLACE VIEW v_active_member_orders AS
SELECT
    m.member_id,
    m.name        AS member_name,
    o.order_id,
    o.total,
    o.ordered_at
FROM members m
JOIN orders o ON m.member_id = o.member_id
WHERE m.status = 'active';

Query OK, 0 rows affected (0.02 sec)

Queries the view just like a regular table.

sample_view.sql

SELECT member_name, SUM(total) AS total_amount
FROM v_active_member_orders
GROUP BY member_id, member_name
ORDER BY total_amount DESC;

+-------------+--------------+
| member_name | total_amount |
+-------------+--------------+
| user_a      |       158000 |
| user_c      |        89500 |
+-------------+--------------+
2 rows in set

Creates a view that filters to active members only (access restriction).

sample_view.sql

CREATE VIEW v_active_members AS
SELECT member_id, name
FROM members
WHERE status = 'active';

Query OK, 0 rows affected

The following is an example:

SELECT * FROM v_active_members;

+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | user_a |
|         2 | user_c |
+-----------+--------+
2 rows in set

Drops the view.

sample_view.sql

DROP VIEW IF EXISTS v_active_member_orders;

Query OK, 0 rows affected

CREATE VIEW and DROP VIEW are standard SQL and work consistently across MySQL, PostgreSQL, and SQLite.

CREATE VIEW v_active_members AS
SELECT member_id, name
FROM members
WHERE status = 'active';

DROP VIEW IF EXISTS v_active_members;

CREATE OR REPLACE VIEW is available in MySQL and PostgreSQL. SQLite does not support it — drop the view first, then recreate it.

-- Recreates a view (SQLite).
DROP VIEW IF EXISTS v_active_members;
CREATE VIEW v_active_members AS
SELECT member_id, name
FROM members
WHERE status = 'active';

A view holds no data of its own — its internal SELECT statement is executed each time the view is queried. This is why it is called a "virtual table." Wrapping complex queries that involve JOINs or aggregations into a view keeps the SQL in your application clean and concise.

Simple views typically support INSERT, UPDATE, and DELETE as well (updatable views). However, views that include aggregate functions, DISTINCT, GROUP BY, UNION, or similar constructs are read-only.

For recursive processing or temporary named queries, consider using WITH (CTE). For SELECT basics, see SELECT.