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;

-- Creates a view that joins active members with their order information.
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';

-- Queries the view just like a regular table.
SELECT member_name, SUM(total) AS total_amount
FROM v_active_member_orders
GROUP BY member_id, member_name
ORDER BY total_amount DESC;

-- Creates an employee view that excludes salary information (access restriction).
CREATE VIEW v_employees_public AS
SELECT employee_id, name, department
FROM employees;

-- Drops the view.
DROP VIEW IF EXISTS v_active_member_orders;

-- Result of: 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 |
-- +-------------+--------------+
-- | Taro Tanaka |       158000 |
-- | Hanako Suzuki |      89500 |
-- | Jiro Yamada |        42000 |
-- +-------------+--------------+

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

CREATE VIEW v_employees_public AS
SELECT employee_id, name, department
FROM employees;

DROP VIEW IF EXISTS v_employees_public;

CREATE OR REPLACE VIEW is available in MySQL, PostgreSQL, and Oracle. In SQL Server, use ALTER VIEW to update a view's definition.

-- Updates a view's definition (SQL Server).
ALTER VIEW v_employees_public AS
SELECT employee_id, name, department, hire_date
FROM employees;

SQLite does not support CREATE OR REPLACE VIEW. To redefine a view, drop it first and then recreate it.

-- Recreates a view (SQLite).
DROP VIEW IF EXISTS v_employees_public;
CREATE VIEW v_employees_public AS
SELECT employee_id, name, department
FROM employees;

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.