SQL Views: Save a Query as a Virtual Table

A view is a saved query with a name. Once created, you can SELECT from it exactly as if it were a table. The view itself does not store data: every time you query it, the database runs the underlying query against the current data.

Syntax#

CREATE VIEW view_name AS
SELECT ...;

Creating a view#

Here is a summary of each customer's orders: a join, a GROUP BY, and some COALESCEs. Rather than rewriting it every time, save it as a view:

CREATE VIEW customer_summary AS
SELECT c.customer_id,
       c.name,
       c.city,
       COUNT(o.order_id)          AS orders,
       COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.city;

Now anyone can use it like a table:

SELECT * FROM customer_summary ORDER BY total_spent DESC;

customer_id | name  | city    | orders | total_spent
------------+-------+---------+--------+------------
2           | Ravi  | Delhi   | 1      | 560
1           | Asha  | Mumbai  | 2      | 370
3           | Meera | Pune    | 0      | 0
4           | John  | Chennai | 0      | 0

And filter it with ordinary WHERE clauses:

SELECT name, total_spent
FROM customer_summary
WHERE orders = 0;

name  | total_spent
------+------------
Meera | 0
John  | 0

A view is always up to date#

Because a view re-runs its query each time, new data appears automatically. Meera places her first order:

INSERT INTO orders (order_id, customer_id, amount, order_date)
VALUES (105, 3, 400, '2026-03-10');

SELECT name, orders, total_spent
FROM customer_summary
WHERE name = 'Meera';

name  | orders | total_spent
------+--------+------------
Meera | 1      | 400

Nothing about the view had to change.

Why use views?#

1. Simplicity: hide a complicated join or calculation behind a simple name. Analysts query customer_summary without needing to know how it is built.
2. Consistency: everyone uses the same definition of "total spent", instead of each person writing a slightly different query.
3. Security: expose only some columns. This view shows employees without salaries or emails:

CREATE VIEW public_employees AS
SELECT id, name, department, city
FROM employees;

SELECT * FROM public_employees;

id | name  | department  | city
---+-------+-------------+----------
1  | Asha  | Sales       | Mumbai
2  | Ravi  | Engineering | Bengaluru
3  | Meera | Engineering | Pune
4  | John  | Marketing   | Mumbai
5  | Priya | Sales       | Delhi

Database administrators can give some users permission to read the view but not the underlying table.

Changing and removing views#

-- Replace the definition (PostgreSQL, MySQL, Oracle)
CREATE OR REPLACE VIEW public_employees AS
SELECT id, name, department
FROM employees;

-- SQL Server 2016 SP1+: CREATE OR ALTER VIEW; older: ALTER VIEW
-- SQLite: DROP VIEW, then CREATE VIEW again

-- Remove a view (the underlying tables are not affected)
DROP VIEW IF EXISTS public_employees;

Can you INSERT or UPDATE through a view?#

Simple views (one table, no GROUP BY, no DISTINCT, no aggregates) are often updatable: an UPDATE public_employees SET city = 'Pune' WHERE id = 4 changes the real employees table. Views with joins, aggregates or DISTINCT, like customer_summary, are read-only. SQLite views are always read-only unless you add triggers.

Views vs CTEs vs tables#

Materialized views#

A materialized view stores the query's result, like a cached table. Reading it is fast, but the data is only as fresh as the last refresh:

-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue;

Oracle also supports materialized views; SQL Server has a related feature called indexed views. MySQL and SQLite do not have them (people use summary tables instead).

Common mistakes#

Practice#

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

SELECT *
FROM dept_stats
WHERE people > 1
ORDER BY avg_salary DESC;

department  | people | avg_salary
------------+--------+-----------
Engineering | 2      | 79500
Sales       | 2      | 56500

Key takeaway#