Views

A view is a named, stored SELECT query that behaves like a virtual table.

Concept

A view is a named, stored SELECT query that behaves like a virtual table. A normal view stores no data; every time you query it, the database runs the underlying query (usually merging it into your query).

A materialized view stores the result of the query physically and must be refreshed to reflect changes in the base tables.

Normal view                         Materialized view
  SELECT * FROM v_sales                 SELECT * FROM mv_sales
          │                                     │
          ▼                                     ▼
  view definition expanded             stored result rows read directly
          │                             (refreshed on schedule / on demand)
          ▼
  base tables read every time

Why It Matters

Simplicity: hide complex joins behind a simple name.

Security: expose only certain columns/rows (e.g., hide salary) and grant access to the view instead of the table.

Consistency: everyone uses the same definition of "active customer" or "net revenue".

Abstraction: base tables can be refactored while the view interface stays stable.

Performance (materialized views): pre-compute expensive aggregations for dashboards.

Syntax

CREATE VIEW view_name [(col_aliases)] AS
SELECT ...;

CREATE OR REPLACE VIEW view_name AS SELECT ...;   -- PostgreSQL, MySQL, Oracle; SQL Server 2016 SP1+: CREATE OR ALTER VIEW
ALTER VIEW view_name AS SELECT ...;               -- SQL Server, MySQL
DROP VIEW [IF EXISTS] view_name;

Example

18.1 CREATE VIEW and SELECT from a view 18.2 Views for security Row-level restriction via view:

18.3 Replacing and altering a view PostgreSQL's CREATE OR REPLACE VIEW can only add columns at the end; it cannot drop or rename existing columns or change their types. Use DROP VIEW + CREATE VIEW for those changes.

18.4 DROP VIEW 18.5 Updatable views A view can accept INSERT/UPDATE/DELETE if the database can map each view row to exactly one base-table row. Typical requirements:

Based on a single table (some DBMSs allow joins if only one table is modified).

No DISTINCT, GROUP BY, HAVING, aggregates, window functions, UNION, or LIMIT.

Columns are simple column references (not expressions) for the columns being updated.

Required (NOT NULL without default) base columns must be in the view for INSERTs.

WITH CHECK OPTION prevents changes through the view that would make the row invisible to the view:

Non-updatable views can be made writable with INSTEAD OF triggers (SQL Server, Oracle, PostgreSQL, SQLite).

18.6 Materialized views Result of SELECT * FROM mv_monthly_revenue ORDER BY month;:

18.7 Normal view vs materialized view No (only the query) Yes (query result)

Stale until refreshed (except SQL Server indexed views / Oracle ON COMMIT) Same as running the underlying query Fast (reads precomputed rows) Disk space for results and indexes

Refresh cost; ON COMMIT refresh slows writes No (except SQL Server indexed views) Simplification, security, abstraction

Dashboards, heavy aggregations, reporting

CREATE VIEW v_order_summary AS
SELECT o.order_id,
       o.order_date,
       c.name       AS customer_name,
       c.country,
       o.status,
       o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT customer_name, SUM(total_amount) AS spent
FROM v_order_summary
WHERE status <> 'CANCELLED'
GROUP BY customer_name
ORDER BY spent DESC;

CREATE VIEW v_employee_directory AS
SELECT emp_id, first_name, last_name, email, dept_id, job_title   -- no salary column
FROM employees;

GRANT SELECT ON v_employee_directory TO reporting_role;
-- reporting_role has no privilege on employees itself

CREATE VIEW v_india_customers AS
SELECT customer_id, name, city
FROM customers
WHERE country = 'India';

-- PostgreSQL / MySQL / Oracle
CREATE OR REPLACE VIEW v_india_customers AS
SELECT customer_id, name, city, email
FROM customers
WHERE country = 'India';

-- SQL Server
CREATE OR ALTER VIEW v_india_customers AS
SELECT customer_id, name, city, email FROM customers WHERE country = 'India';

DROP VIEW IF EXISTS v_india_customers;
DROP VIEW v_order_summary CASCADE;   -- PostgreSQL: also drops views that depend on it

UPDATE v_india_customers SET city = 'Navi Mumbai' WHERE customer_id = 1;   -- updates customers

CREATE VIEW v_india_customers AS
SELECT customer_id, name, city, country
FROM customers
WHERE country = 'India'
WITH CHECK OPTION;

UPDATE v_india_customers SET country = 'UK' WHERE customer_id = 1;
-- ERROR: new row violates check option for view "v_india_customers"

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date)::DATE AS month,
       SUM(total_amount) AS revenue,
       COUNT(*) AS orders
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY DATE_TRUNC('month', order_date);

SELECT * FROM mv_monthly_revenue ORDER BY month;

REFRESH MATERIALIZED VIEW mv_monthly_revenue;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;   -- needs a UNIQUE index; readers not blocked
CREATE UNIQUE INDEX ON mv_monthly_revenue (month);

-- Oracle
CREATE MATERIALIZED VIEW mv_monthly_revenue
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND        -- or REFRESH FAST ON COMMIT with materialized view logs
AS
SELECT TRUNC(order_date, 'MM') AS month, SUM(total_amount) AS revenue
FROM orders WHERE status <> 'CANCELLED'
GROUP BY TRUNC(order_date, 'MM');

-- SQL Server: "indexed views" (a view WITH SCHEMABINDING + a unique clustered index)
CREATE VIEW dbo.v_product_sales WITH SCHEMABINDING AS
SELECT oi.product_id, SUM(oi.quantity) AS units, COUNT_BIG(*) AS cnt
FROM dbo.order_items oi
GROUP BY oi.product_id;
CREATE UNIQUE CLUSTERED INDEX ix_v_product_sales ON dbo.v_product_sales (product_id);
-- Maintained automatically on e
…

Expected Result

SELECT * FROM v_employee_directory WHERE dept_id = 3;

Common Mistakes

Believing a normal view makes queries faster (it does not store results).

Nesting views many levels deep, producing huge hidden queries that are hard to optimize.

SELECT * in a view definition: in many DBMSs the column list is fixed at creation time, so new base-table columns do not appear (and dropped columns break the view).

Forgetting to refresh materialized views, leading to stale dashboards.

Using ORDER BY inside a view and expecting ordered results from queries on it.

Trying to update a view with GROUP BY or joins.

Best Practices

Prefix view names (v_, mv_) or use a dedicated schema.

List explicit columns in view definitions.

Use views as a security boundary for sensitive columns.

Schedule materialized view refreshes and monitor their duration.

Keep view nesting shallow (at most 2 levels is a good guideline).

Interview Relevance

Medium-high: "What is a view?", "Does a view store data?", "View vs materialized view?", "Can you update a view?", "What is WITH CHECK OPTION?", "View vs table?".

Practice Questions

Create a view showing product name, category, and units sold.

Create a view of employees with their department name and manager name.

Try to update a row through a view with GROUP BY and explain the error.

When would you choose a materialized view over a normal view?

Create a view for "active customers" (at least one non-cancelled order) and use it in a query.