SQL Views – Saving Queries as Virtual Tables

What Is a View?

A view is a virtual table defined by a SELECT query. When you query a view, the database executes the underlying SELECT and returns the results as if they were rows in a table. The data is not stored separately — the view is just a saved query definition.

ℹ️

View vs Table

A regular table stores data on disk. A view stores only the query definition — every time you SELECT from a view, the database re-runs the query and returns fresh data. This means a view always reflects the current state of the underlying tables.

Creating a View

Use CREATE VIEW followed by the view name and the SELECT query that defines it.

SQL

-- Create a view that filters to active employees only
CREATE VIEW active_employees AS
  SELECT id, name, department, salary, hire_date
  FROM employees
  WHERE status = 'active';

-- Create a view that encapsulates a complex join
CREATE VIEW order_summary AS
  SELECT
    o.id        AS order_id,
    c.name      AS customer_name,
    c.email     AS customer_email,
    o.total     AS order_total,
    o.created_at
  FROM orders o
  JOIN customers c ON c.id = o.customer_id;

-- Update an existing view without dropping it first
CREATE OR REPLACE VIEW active_employees AS
  SELECT id, name, department, salary, hire_date, email
  FROM employees
  WHERE status = 'active';

Querying Views

Once created, query a view exactly like a table — use WHERE, ORDER BY, JOIN, GROUP BY, etc.

SQL

-- Simple select from view
SELECT * FROM active_employees;

-- Add filters on top of the view
SELECT * FROM active_employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

-- Aggregate on a view
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM active_employees
GROUP BY department;

-- Join a view with another table
SELECT ae.name, ae.department, m.name AS manager
FROM active_employees ae
JOIN employees m ON m.id = ae.manager_id;

💡

Views are transparent to the query optimizer

Most modern databases "inline" view definitions into the outer query during query planning. This means the optimizer can apply your WHERE clause filters before running the full view query — you don't always pay for computing the entire view result set.

Updatable Views

In some cases you can INSERT, UPDATE, and DELETE through a view. The rules vary by database, but the general requirement is that the view must be simple: it queries a single table, has no aggregation, no DISTINCT, no GROUP BY, and no subqueries.

SQL

-- This simple view is updatable
CREATE VIEW engineering_staff AS
  SELECT id, name, salary
  FROM employees
  WHERE department = 'Engineering';

-- Update through the view — modifies the underlying employees table
UPDATE engineering_staff
SET salary = salary * 1.10
WHERE name = 'Alice';

-- Insert through the view
INSERT INTO engineering_staff (name, salary)
VALUES ('Bob', 95000);
-- Note: 'department' column gets its DEFAULT value in the base table

-- WITH CHECK OPTION prevents inserting rows that don't match the view's WHERE
CREATE OR REPLACE VIEW engineering_staff AS
  SELECT id, name, salary, department
  FROM employees
  WHERE department = 'Engineering'
  WITH CHECK OPTION;

-- This INSERT would FAIL because 'Marketing' doesn't match the view filter
INSERT INTO engineering_staff (name, salary, department)
VALUES ('Carol', 80000, 'Marketing'); -- ERROR

Dropping and Replacing Views

SQL

-- Drop a view
DROP VIEW active_employees;

-- Drop only if it exists (no error if it doesn't)
DROP VIEW IF EXISTS active_employees;

-- Update (replace) an existing view — no need to drop first
CREATE OR REPLACE VIEW active_employees AS
  SELECT id, name, department, salary, email, hire_date
  FROM employees
  WHERE status = 'active';

-- List all views in the current database (PostgreSQL)
SELECT table_name FROM information_schema.views
WHERE table_schema = 'public';

-- List all views (MySQL)
SHOW FULL TABLES WHERE Table_type = 'VIEW';

Practical Use Cases

1. Simplify complex joins

SQL

-- Without a view: repeated complex join in every report query
SELECT p.name, c.name AS category, s.quantity, s.sold_at
FROM sales s
JOIN products p ON p.id = s.product_id
JOIN categories c ON c.id = p.category_id
WHERE s.sold_at >= '2024-01-01';

-- With a view: define it once, query it simply
CREATE VIEW sales_with_details AS
  SELECT s.id, p.name AS product, c.name AS category,
         s.quantity, s.sold_at
  FROM sales s
  JOIN products p ON p.id = s.product_id
  JOIN categories c ON c.id = p.category_id;

SELECT * FROM sales_with_details WHERE sold_at >= '2024-01-01';

2. Security — restrict column access

SQL

-- Expose only safe columns to reporting users
CREATE VIEW employees_public AS
  SELECT id, name, department, job_title
  FROM employees;
  -- salary, ssn, home_address are intentionally excluded

-- Grant SELECT on the view only — reporting users never see sensitive columns
GRANT SELECT ON employees_public TO reporting_role;

3. Materialized views (PostgreSQL)

A materialized view actually stores the query results on disk, unlike a regular view. Reads are fast because no re-computation happens, but the data can be slightly stale until you refresh it.

SQL

-- Create a materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total)                       AS revenue,
    COUNT(*)                         AS order_count
  FROM orders
  GROUP BY 1
  ORDER BY 1;

-- Query it like any table — data is pre-computed, very fast
SELECT * FROM monthly_revenue WHERE month >= '2024-01-01';

-- Refresh (re-run the query and update stored data)
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh without locking reads (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

ℹ️

When to use materialized views

Use materialized views for expensive aggregate queries that are run frequently but whose results don't need to be real-time — for example, dashboards, nightly reports, or analytics summaries. Schedule a REFRESH via a cron job or a database scheduler to keep data acceptably fresh.

Summary

📋 Summary

A view is a named saved query that behaves like a table but stores no data.
Create with CREATE VIEW name AS SELECT ...; replace in-place with CREATE OR REPLACE VIEW.
Query views exactly like tables — WHERE, JOIN, GROUP BY, ORDER BY all work.
Simple single-table views are updatable; complex views (joins, aggregates) are read-only.
Use WITH CHECK OPTION to prevent DML that would produce rows invisible to the view.
Use views to simplify joins, enforce security (hide columns), and create a stable interface over changing schema.
Materialized views (PostgreSQL) store results on disk for fast reads; run REFRESH MATERIALIZED VIEW to update.

FAQ

Does a view slow down my queries? +

Not inherently. Most database engines "inline" a view definition — they merge the view's SQL with your outer query and optimize the combined plan. However, a very complex view with many joins may be harder to optimize than an equivalent raw query. If performance is critical, benchmark both approaches or consider a materialized view.

Can I create a view based on another view? +

Yes — views can reference other views. However, deeply nested view chains can make query plans complex and hard to debug. A better practice is often to use a CTE (Common Table Expression) inside a single query, or to flatten the view definitions when performance becomes an issue.

What is the difference between a view and a CTE? +

A CTE (WITH ... AS (...)) is temporary — it exists only for the duration of the single query it is defined in. A view is persistent — it is saved in the database and can be reused across many queries and sessions. Use a CTE for a one-off query breakdown; use a view when many queries need the same sub-result.

Can I add an index to a view? +

Not on a regular view (which stores no data). You can add indexes to a materialized view in PostgreSQL because the data is physically stored. In SQL Server, "indexed views" are also supported — these are equivalent to materialized views. In MySQL and SQLite, regular views cannot have indexes.