Skip to content

Views

Create virtual tables with views: simplifying queries, security, and materialized views.

What you'll learn

  • What a view is and how it acts as a virtual table
  • Creating views to simplify complex queries
  • Using views for security and abstraction
  • Updatable vs non-updatable views
  • Materialized views for caching expensive queries

Concept

What Is a View?

A view is a named, stored query that behaves like a virtual table. It does not store data itself (unless it is a materialized view) — it stores a SELECT statement and runs it every time you query the view. Views are useful for simplifying complex queries, providing a stable API over changing schemas, and restricting access to sensitive columns.

Simplifying Complex Queries

If you frequently join four tables with the same conditions, you can create a view that encapsulates that join. Then applications query the view as if it were a single table. This keeps application SQL simple and centralizes the join logic in one place. If the underlying schema changes, you update the view definition and all consumers automatically adapt.

Security and Abstraction

Views can hide columns or rows from users. You can grant a user SELECT access to a view without granting access to the underlying tables. For example, a employee_public view that excludes the salary column lets a reporting tool see employee data without exposing salaries. This is column-level security via views.

Updatable Views

In many databases, a simple view (single table, no aggregates, no DISTINCT, no JOIN) is updatable — you can INSERT, UPDATE, and DELETE through the view. Complex views with joins or aggregates are read-only. Check your database documentation for the exact rules.

Materialized Views

A materialized view stores the query result physically, like a real table. It is refreshed on demand (REFRESH MATERIALIZED VIEW). This is far faster for expensive aggregate queries that do not need real-time data. The trade-off is staleness — the materialized view may not reflect the latest changes until you refresh it.

Example

              -- Create a view that simplifies a common join
CREATE VIEW order_summary AS
SELECT
  u.name        AS customer_name,
  o.id          AS order_id,
  o.total       AS order_total,
  o.status      AS order_status,
  o.created_at  AS order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status != 'cancelled';

-- Query the view like a regular table
SELECT * FROM order_summary
WHERE order_date >= '2025-01-01'
ORDER BY order_total DESC;

-- A view for security: hide the salary column
CREATE VIEW employee_directory AS
SELECT id, name, department, email FROM employees;

-- Materialized view for an expensive aggregate (PostgreSQL)
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  DATE(created_at) AS day,
  SUM(total)       AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW daily_revenue;
            

Four view patterns: a simplifying join view, querying it, a security view that hides salary, and a materialized view for daily revenue with refresh.

Try it

  • SQL Formatter

    Format CREATE VIEW and materialized view definitions.

  • JSON Path

    JSONPath projections are similar to views — select a subset.

Common mistakes

The mistake

Creating a view over a view over a view, hurting performance

The fix

Each view layer adds query complexity. The database usually flattens nested views, but deeply nested views are hard to debug. Keep view nesting shallow.

The mistake

Forgetting to refresh materialized views

The fix

Materialized views go stale. Set up a scheduled refresh (cron job or trigger) based on how fresh the data needs to be.

Practice Views

2 exercises

Practice Views

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors