A view is a saved query that behaves like a table. It stores no data (unless materialised) — every time you select from it, its query runs.

Creating and using a view

SQL

CREATE VIEW employee_directory AS
SELECT e.emp_id, e.name, d.name AS department, d.location, m.name AS manager
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
LEFT JOIN employees  m ON m.emp_id  = e.manager_id;

SELECT name, department, manager
FROM employee_directory
WHERE location = 'Mumbai'
ORDER BY emp_id;

Output

name  | department | manager
------+------------+--------
Priya | Sales      | Asha
Arjun | Sales      | Priya
Neha  | Sales      | Priya

A view for business logic

SQL

CREATE VIEW order_summary AS
SELECT o.order_id, o.customer_id, o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS order_total,
       COUNT(*) AS line_count
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;

SELECT status, COUNT(*) AS orders, SUM(order_total) AS value
FROM order_summary
GROUP BY status
ORDER BY value DESC;

Output

status    | orders | value
----------+--------+-------
delivered | 4      | 119100
shipped   | 2      | 23000
cancelled | 1      | 5000
pending   | 1      | 1200

Updatable views

A simple view over one table without aggregates, DISTINCT or GROUP BY is usually updatable in PostgreSQL, MySQL, SQL Server and Oracle. WITH CHECK OPTION rejects writes that would fall outside the view's WHERE.

PostgreSQL / MySQL / SQL Server / Oracle

CREATE VIEW sales_team AS
SELECT emp_id, name, salary, dept_id FROM employees WHERE dept_id = 2
WITH CHECK OPTION;

UPDATE sales_team SET salary = salary + 1000 WHERE emp_id = 6;   -- OK: updates employees
UPDATE sales_team SET dept_id = 3 WHERE emp_id = 6;              -- ERROR: row would leave the view

Materialised views

A materialised view stores the query result physically, so reads are fast but data is only as fresh as the last refresh. Great for heavy dashboards.

PostgreSQL (Oracle: MATERIALIZED VIEW; SQL Server: indexed views)

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', order_date) AS month, SUM(total) AS revenue
FROM order_summary GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;   -- needs a unique index

	View	Materialised view
Stores data	No — query runs on every read	Yes — a snapshot
Freshness	Always current	Stale until refreshed
Read speed	Same as the underlying query	Fast (can be indexed)
Write cost	None	Refresh cost

What is the difference between a view and a table?

Can you insert into a view?