SQL Views — The 12M Row JOIN That Crashed Your Dashboard

Every production database grows complicated fast. A single order in an e-commerce system might touch five or six tables — customers, orders, order_items, products, addresses, and discounts. If every developer on your team writes their own join query to pull a customer order summary, you get six slightly different versions of the truth, and the day a column gets renamed you're hunting down broken queries across a dozen files. SQL Views were built precisely to kill that problem before it kills your sanity.

A view wraps a complex query into a single named object that lives inside the database. Anyone who needs that data just selects from the view — they don't need to know or care about the joins, filters, or subqueries underneath. When the underlying structure changes, you update the view in one place and every consumer is instantly fixed. Beyond simplicity, views also act as a security layer: you can grant a user access to a view that shows only certain columns or rows, without ever giving them access to the raw tables.

By the end of this article you'll know exactly when to reach for a view instead of repeating a query, how to build both read-only and updatable views, how to use views as a security boundary, and — critically — the limitations that trip up even experienced developers. You'll walk away with patterns you can drop into a real project today.

Why SQL Views Are Not Just Saved Queries

A SQL view is a virtual table defined by a SELECT statement that the database stores as a schema object. Unlike a materialized view, a standard view does not hold data — it's a saved query that runs every time you reference it. The core mechanic: the database engine merges the view's definition into the outer query during planning, then executes the combined statement against the underlying tables.

When you query a view, the optimizer expands it inline. This means every column reference, filter, and join in the view becomes part of the final execution plan. A view that joins 12 million rows across four tables will re-execute that join on every access unless the database caches the result. Indexes on the base tables still apply, but the view itself cannot be indexed. Performance depends entirely on how the optimizer rewrites the query — and it often fails to push predicates down through complex views, causing full table scans.

Use views to enforce column-level security, abstract table schemas, or provide a stable interface for reporting. But never assume a view is free. In production, a view that wraps a heavy join will crash dashboards when users filter by a column the optimizer cannot push down. The rule: treat a view as a macro that must be analyzed with EXPLAIN, not a precomputed result.

Creating Your First View — and Understanding What Actually Happens

When you run CREATE VIEW, the database doesn't execute the query and store a snapshot of the results. It stores the query definition itself. Every time someone SELECTs from the view, the database runs that stored query fresh against the live tables. This is the single most important thing to understand about views: they are not cached copies of data. They are reusable, named queries.

This design has a beautiful consequence. If a new order is inserted into the orders table at 2pm, anyone querying the view at 2:01pm sees it immediately — no refresh, no sync, no ETL job needed.

Start with a realistic scenario. You have an e-commerce database. Business stakeholders constantly ask: 'Show me each customer's name, their total number of orders, and their lifetime spend.' Writing that join every time is tedious and error-prone. A view makes it a one-liner for every future query.

The syntax is simple, but the thinking behind it matters more than the keywords. You're essentially giving a SELECT statement a permanent name and home inside your database schema.

-- ─────────────────────────────────────────────────────────────
-- SETUP: Create the base tables we'll build our view on top of
-- ─────────────────────────────────────────────────────────────
CREATE TABLE customers (
    customer_id   INT          PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    email         VARCHAR(150) NOT NULL,
    country       VARCHAR(50)  NOT NULL
);

CREATE TABLE orders (
    order_id     INT           PRIMARY KEY,
    customer_id  INT           NOT NULL REFERENCES customers(customer_id),
    order_date   DATE          NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL
);

-- ─────────────────────────────────────────────────────────────
-- SEED DATA: Realistic rows so we can see real output
-- ─────────────────────────────────────────────────────────────
INSERT INTO customers VALUES
    (1, 'Amara Osei',    'amara@example.com',  'Ghana'),
    (2, 'Lena Fischer',  'lena@example.com',   'Germany'),
    (3, 'Carlos Ruiz',   'carlos@example.com', 'Mexico');

INSERT INTO orders VALUES
    (101, 1, '2024-01-15',  89.99),
    (102, 1, '2024-03-22', 214.50),
    (103, 2, '2024-02-10',  49.00),
    (104, 3, '2024-04-01', 320.00),
    (105, 3, '2024-04-18',  75.25);

-- ─────────────────────────────────────────────────────────────
-- THE VIEW: Stores the query definition, NOT the result data.
-- Every SELECT against this view re-runs the JOIN live.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.full_name,
    c.country,
    COUNT(o.order_id)       AS total_orders,      -- aggregates across all rows for this customer
    SUM(o.total_amount)     AS lifetime_spend,    -- total money spent, ever
    MAX(o.order_date)       AS last_order_date    -- most recent purchase date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id  -- LEFT JOIN keeps customers with zero orders
GROUP BY
    c.customer_id,
    c.full_name,
    c.country;

-- ─────────────────────────────────────────────────────────────
-- USING THE VIEW: Looks exactly like querying a table
-- ─────────────────────────────────────────────────────────────
SELECT
    full_name,
    total_orders,
    lifetime_spend
FROM customer_order_summary
WHERE lifetime_spend > 100.00
ORDER BY lifetime_spend DESC;

Views as a Security Layer — Hide Columns, Filter Rows, Control Access

This is the use case that makes views indispensable in production systems — and the one most tutorials skip entirely. Views let you expose only the data a given role should see, without duplicating tables or writing application-level filters that can be bypassed.

Consider a HR system. The employees table holds salary, bank account numbers, and performance review scores alongside public info like name and department. Your operations team needs to look up who's in which department. Your payroll team needs salary data. Under no circumstances should either team see the other's sensitive columns.

Instead of granting direct table access, you create purpose-built views for each audience, grant SELECT on the view, and explicitly deny SELECT on the raw table. The database enforces this at the engine level — no application bug can accidentally leak a salary figure through a view that was never designed to show one.

Row-level filtering works the same way. A regional manager should only see employees in their region. You bake that WHERE clause into the view definition. They literally cannot query rows outside their region, because the view definition never fetches them.

-- ─────────────────────────────────────────────────────────────
-- BASE TABLE: Contains sensitive columns alongside public ones
-- ─────────────────────────────────────────────────────────────
CREATE TABLE employees (
    employee_id    INT           PRIMARY KEY,
    full_name      VARCHAR(100)  NOT NULL,
    department     VARCHAR(80)   NOT NULL,
    region         VARCHAR(50)   NOT NULL,
    annual_salary  NUMERIC(12,2) NOT NULL,   -- SENSITIVE
    bank_account   VARCHAR(30)   NOT NULL,   -- SENSITIVE
    hire_date      DATE          NOT NULL
);

INSERT INTO employees VALUES
    (1, 'Yuki Tanaka',    'Engineering', 'APAC',   95000.00, 'GB29NWBK60161331926819', '2021-06-01'),
    (2, 'Sofia Delgado',  'HR',          'EMEA',   72000.00, 'DE89370400440532013000', '2020-03-15'),
    (3, 'Marcus Webb',    'Engineering', 'EMEA',   88000.00, 'FR7614508059405402982935', '2019-11-22'),
    (4, 'Priya Sharma',   'Sales',       'APAC',   67000.00, 'IN30267931234567890123', '2022-09-10');

-- ─────────────────────────────────────────────────────────────
-- VIEW 1: Operations team — sees name, department, region only.
-- Salary and bank account columns are simply not included.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW employee_directory AS
SELECT
    employee_id,
    full_name,
    department,
    region,
    hire_date
FROM employees;  -- no salary, no bank_account — they don't exist in this view

-- ─────────────────────────────────────────────────────────────
-- VIEW 2: Payroll team — salary visible, but still no bank
-- account number (that's only for the finance system to access)
-- ─────────────────────────────────────────────────────────────
CREATE VIEW employee_payroll_summary AS
SELECT
    employee_id,
    full_name,
    department,
    annual_salary
FROM employees;  -- bank_account intentionally excluded

-- ─────────────────────────────────────────────────────────────
-- VIEW 3: EMEA regional manager — row-level restriction.
-- This view physically cannot return rows from other regions.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW emea_employee_directory AS
SELECT
    employee_id,
    full_name,
    department,
    hire_date
FROM employees
WHERE region = 'EMEA';  -- the filter lives in the database, not the application

-- ─────────────────────────────────────────────────────────────
-- GRANT access to the VIEW only. The ops_user role never gets
-- SELECT on the raw employees table.
-- ─────────────────────────────────────────────────────────────
-- GRANT SELECT ON employee_directory TO ops_user;
-- REVOKE SELECT ON employees FROM ops_user;

-- Test the EMEA view — only EMEA rows should appear
SELECT * FROM emea_employee_directory ORDER BY full_name;

Updatable Views vs. Read-Only Views — Knowing Which is Which

Not all views are equal when it comes to writing data back through them. Some views allow INSERT, UPDATE, and DELETE — these are called updatable views. Others are permanently read-only. Knowing the rules prevents runtime errors and design mistakes.

A view is updatable when it maps cleanly to a single base table with no transformation: no GROUP BY, no aggregate functions like SUM or COUNT, no DISTINCT, no subqueries in the SELECT list, and no JOINs that would make row identity ambiguous. If the database can figure out exactly which row in exactly which table to touch, it'll allow the write.

The moment you add an aggregate, a GROUP BY, or a JOIN across multiple tables, the view becomes read-only. This makes sense when you think about it: if a view row represents the SUM of five order rows, which of those five rows should an UPDATE actually modify?

Materialized views are a separate concept (available in PostgreSQL, Oracle, and others) where the query result IS physically stored. They're fast to read but require explicit refreshing. The trade-off is freshness vs. performance — crucial for dashboards and reporting.

-- ─────────────────────────────────────────────────────────────
-- SETUP
-- ─────────────────────────────────────────────────────────────
CREATE TABLE products (
    product_id   INT           PRIMARY KEY,
    product_name VARCHAR(100)  NOT NULL,
    category     VARCHAR(50)   NOT NULL,
    unit_price   NUMERIC(8,2)  NOT NULL,
    is_active    BOOLEAN       NOT NULL DEFAULT TRUE
);

INSERT INTO products VALUES
    (1, 'Wireless Keyboard', 'Electronics', 49.99, TRUE),
    (2, 'Desk Lamp',         'Office',      29.99, TRUE),
    (3, 'Ergonomic Chair',   'Furniture',  349.00, FALSE),
    (4, 'USB-C Hub',         'Electronics', 34.99, TRUE);

-- ─────────────────────────────────────────────────────────────
-- UPDATABLE VIEW: Single table, no aggregates, no DISTINCT.
-- The database can map each view row to exactly one table row.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW active_products AS
SELECT
    product_id,
    product_name,
    category,
    unit_price
FROM products
WHERE is_active = TRUE;  -- simple filter, view is still updatable

-- This UPDATE works — the database knows exactly which row to change
UPDATE active_products
SET unit_price = 54.99
WHERE product_id = 1;  -- maps cleanly to products row with product_id = 1

-- Confirm the change made it through to the base table
SELECT product_id, product_name, unit_price FROM products WHERE product_id = 1;

-- ─────────────────────────────────────────────────────────────
-- READ-ONLY VIEW: Uses GROUP BY + COUNT — not updatable.
-- The database can't reverse-engineer which base rows to touch.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW product_count_by_category AS
SELECT
    category,
    COUNT(*)          AS product_count,
    AVG(unit_price)   AS avg_price
FROM products
GROUP BY category;

-- This would FAIL with: ERROR: cannot update a non-updatable view
-- UPDATE product_count_by_category SET avg_price = 40.00 WHERE category = 'Electronics';
-- (Commented out so the script runs cleanly — uncomment to see the error)

-- Safe to SELECT from it though
SELECT * FROM product_count_by_category ORDER BY category;

Replacing and Dropping Views — Managing Views in a Real Schema

Views aren't fire-and-forget. Business requirements change, columns get renamed, and performance optimizations force you to refactor the underlying query. Knowing how to safely update and remove views without taking down dependent code is a practical skill that separates juniors from seniors.

CREATE OR REPLACE VIEW lets you redefine a view's query in place, without dropping dependent objects or revoking existing permissions. There's one catch: you can't remove columns from the SELECT list using REPLACE — you can only add new ones or change existing expressions. Removing columns requires a DROP followed by a CREATE.

Before dropping a view, check whether other views, stored procedures, or application queries depend on it. Most databases give you a system catalog to query for this. Dropping a view with CASCADE will also drop anything that depends on it — a powerful option that can silently destroy more than you intended.

Replacing the query inside a view is also how you fix a performance problem. If you realize the underlying JOIN was written inefficiently, you REPLACE the view with an optimized query and every caller instantly benefits, with no code changes outside the database.

-- ─────────────────────────────────────────────────────────────
-- ORIGINAL VIEW (from our first section)
-- ─────────────────────────────────────────────────────────────
-- CREATE VIEW customer_order_summary AS ... (already created above)

-- ─────────────────────────────────────────────────────────────
-- SCENARIO: Business now wants average order value added.
-- Use CREATE OR REPLACE — keeps existing GRANTs and dependencies.
-- ─────────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.full_name,
    c.country,
    COUNT(o.order_id)                       AS total_orders,
    SUM(o.total_amount)                     AS lifetime_spend,
    ROUND(AVG(o.total_amount), 2)           AS avg_order_value,  -- NEW column added safely
    MAX(o.order_date)                       AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.full_name,
    c.country;

-- ─────────────────────────────────────────────────────────────
-- CHECK DEPENDENCIES before dropping (PostgreSQL syntax).
-- Run this BEFORE any DROP VIEW to see what you'd break.
-- ─────────────────────────────────────────────────────────────
SELECT
    dependent_view.relname   AS view_that_depends,
    source_view.relname      AS depends_on_this_view
FROM pg_depend dep
JOIN pg_rewrite rw       ON dep.objid      = rw.oid
JOIN pg_class dependent_view ON rw.ev_class = dependent_view.oid
JOIN pg_class source_view    ON dep.refobjid = source_view.oid
WHERE source_view.relname = 'customer_order_summary'  -- replace with your view name
  AND dependent_view.relname != source_view.relname;  -- exclude self-reference

-- ─────────────────────────────────────────────────────────────
-- SAFE DROP: No cascade — fails loudly if dependencies exist.
-- That loud failure is a feature, not a bug.
-- ─────────────────────────────────────────────────────────────
-- DROP VIEW customer_order_summary;                 -- safe: errors if depended on
-- DROP VIEW customer_order_summary CASCADE;         -- dangerous: silently drops dependents

-- Verify the updated view includes the new column
SELECT
    full_name,
    total_orders,
    lifetime_spend,
    avg_order_value
FROM customer_order_summary
ORDER BY lifetime_spend DESC;

The WITH CHECK OPTION — Preventing Data Corruption Through Your Views

You built a view that filters rows. A junior runs an UPDATE through it. Suddenly rows disappear from the view. The data didn't vanish — the update pushed those rows outside the view's filter predicate. Now nobody can see them through that view, but they're still in the base table. That's a silent schema violation.

WITH CHECK OPTION forces every write through the view to satisfy the WHERE clause. PostgreSQL, SQL Server, MySQL, Oracle — they all support it, but most devs never use it. The result: views that leak data you thought you'd locked down.

When you define a view as CREATE VIEW active_orders AS SELECT * FROM orders WHERE status = 'active' WITH CHECK OPTION, any UPDATE setting status to 'cancelled' or INSERT with status 'archived' gets rejected. The database enforces the boundary. No surprises. No midnight debugging sessions because "the view just lost 200 rows."

Always add WITH CHECK OPTION on updatable views with filters. Your future self will thank you when a data pipeline doesn't silently eat records.

// io.thecodeforge — database tutorial

-- Bad: no check option allows updates that make rows invisible
CREATE VIEW active_orders AS
SELECT id, customer_id, status, total
FROM orders
WHERE status = 'active';

-- This succeeds but row vanishes from view
UPDATE active_orders SET status = 'cancelled' WHERE id = 1042;

-- Good: WITH CHECK OPTION prevents the escape
CREATE VIEW active_orders_safe AS
SELECT id, customer_id, status, total
FROM orders
WHERE status = 'active'
WITH CHECK OPTION;

-- This now fails with:
-- ERROR: new row violates WITH CHECK OPTION for view "active_orders_safe"
UPDATE active_orders_safe SET status = 'cancelled' WHERE id = 1042;

Materialized Views — When a Virtual Table Isn't Fast Enough

Standard views are just query macros. Every SELECT re-executes the entire underlying query. Fine for light filters. Terrible for aggregates on million-row tables. Your dashboard query that joins five tables and runs GROUP BY on 3M rows? That view takes 12 seconds every page load.

Materialized views cache the result as an actual table. The database updates it on a schedule or on demand. PostgreSQL calls them MATERIALIZED VIEW. SQL Server calls them indexed views. BigQuery has them natively. Oracle has had them since the 90s.

The trade-off: you get read performance of a table with the logical abstraction of a view. The cost is staleness and storage. You refresh the materialized view with REFRESH MATERIALIZED VIEW monthly_sales_summary — but between refreshes, the data lags behind the base tables.

Use materialized views for: reporting aggregates, warehouse-style rollups, cross-database joins where latency kills you. Don't use them for real-time operational queries. Know your refresh tolerance before you build.

// io.thecodeforge — database tutorial

-- Slow: standard view recalculates every query
CREATE VIEW daily_sales_report AS
SELECT DATE(created_at) AS sale_date,
       product_id,
       COUNT(*) AS units_sold,
       SUM(amount) AS revenue
FROM transactions
GROUP BY DATE(created_at), product_id;

-- Fast: materialized view stores result (PostgreSQL syntax)
CREATE MATERIALIZED VIEW daily_sales_materialized AS
SELECT DATE(created_at) AS sale_date,
       product_id,
       COUNT(*) AS units_sold,
       SUM(amount) AS revenue
FROM transactions
GROUP BY DATE(created_at), product_id
WITH DATA;

-- Refresh on schedule (cron job or pg_cron)
REFRESH MATERIALIZED VIEW daily_sales_materialized;

Nested Views — Why Your View Should Call Another View

A view is a table to another view. That isn't a bug — it's a weapon. Real schemas stack views to decompose complex logic into testable layers. Instead of one 200-line monster, you build a base view that cleans the data, a mid-level view that joins domains, and an outer view that applies security policies.

This isn't just neat. It's survivable. When your CEO asks for a report that excludes archived orders, you change one base view. Every dependent view picks it up. No cascading rewrites. No hunting for copy-pasted WHERE clauses across ten files.

Production trap: nested views can hit recursion limits. PostgreSQL defaults to 100. MySQL hates deep stacks. Keep it under 3 levels. If your stack is deeper, you're abusing views — that's a stored procedure or a CTE screaming to be born.

// io.thecodeforge — database tutorial

-- Base view: clean raw orders
CREATE VIEW v_clean_orders AS
SELECT id, customer_id, amount, order_date
FROM raw_orders
WHERE deleted_at IS NULL;

-- Mid view: join with geography
CREATE VIEW v_regional_orders AS
SELECT o.id, o.amount, c.region
FROM v_clean_orders o
JOIN customers c ON c.id = o.customer_id;

-- Outer view: apply security filter
CREATE VIEW v_eu_orders AS
SELECT * FROM v_regional_orders
WHERE region = 'EU';

Listing & Documenting Views — Your Schema Isn't Self-Explaining

Production schemas accumulate views like dust. Six months in, nobody remembers why v_old_reports exists. Two problems: finding what you have, and knowing what it does. SQL gives you metadata commands for both.

PostgreSQL's \dv or information_schema.views lists every view with its definition. MySQL's SHOW FULL TABLES marks views. Use these to audit before you deploy. Better yet — put a COMMENT on every view. That comment survives migrations, survives team turnover, and shows up in pg_stat_statements when the DBA asks why someone querying v_old_reports is causing a seq scan.

Senior move: query information_schema.views with a WHERE clause to find views using deprecated columns. If you renamed status to order_status last month, find every view that still says status before it breaks production at 3 AM.

// io.thecodeforge — database tutorial

-- PostgreSQL: list all views with comments
SELECT v.table_name,
       pg_catalog.obj_description(c.oid, 'pg_class') AS comment
FROM information_schema.views v
JOIN pg_catalog.pg_class c ON c.relname = v.table_name
WHERE v.table_schema = 'public';

-- Then add or update a comment
COMMENT ON VIEW v_eu_orders IS
  'EU customer orders, excludes archived records. Updated daily by ETL job. Contact: dba@example.com';

Advanced Techniques with Views — Beyond Basic Abstraction

Views can do more than hide columns or simplify joins. Use views to enforce row-level security through dynamic predicates like WHERE user_id = current_user_id(), ensuring tenants only see their own data without application-level filters. Combine views with window functions to create rolling aggregates — a weekly sales view that always reflects the last 7 days without manual queries. For schema evolution, build versioned views (e.g., customer_v2) that map old column names to new ones, allowing backward compatibility while you migrate clients. Another pattern: union views that merge identical structures from multiple sharded tables — a sales_global view that UNION ALL from sales_us and sales_eu, simplifying cross-region reporting. These techniques shift complexity from application code to the database, where set operations are optimized. Warning: overuse can create debugging nightmares. Always test view query plans with EXPLAIN ANALYZE.

// io.thecodeforge — database tutorial

CREATE VIEW tenant_orders AS
SELECT id, product, amount
FROM orders
WHERE tenant_id = current_setting('app.current_tenant_id')::int;

CREATE VIEW weekly_sales AS
SELECT product,
       SUM(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_day
FROM orders;

CREATE VIEW v2_customer AS
SELECT id, full_name AS name, email
FROM customer_v1;

Common View Tasks — What You Actually Do with Them

Three frequent tasks dominate real-world view usage: refreshing stale aggregates, documenting view purpose, and checking updatability. For materialized views, run REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid locking reads — schedule it during low traffic. Document views with COMMENT ON VIEW — this metadata survives schema exports and ORM introspection. To list all views, query information_schema.views or pg_views (Postgres) — filter by schema and check is_updatable column to separate read-only from updatable views. When debugging, use SHOW CREATE VIEW (MySQL) or pg_get_viewdef() (Postgres) to recover the definition verbatim. For renaming, always test dependencies with SELECT * FROM information_schema.view_table_usage WHERE view_name='...'. A common mistake: dropping a view that other views depend on — you'll get a cascade error. Use DROP VIEW IF EXISTS ... RESTRICT to fail safely.

// io.thecodeforge — database tutorial

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

COMMENT ON VIEW customer_orders IS 'Shows orders for active customers only';

SELECT table_name, is_updatable
FROM information_schema.views
WHERE table_schema = 'public';

-- Recover definition
SELECT pg_get_viewdef('customer_orders', true);

-- Safe drop
DROP VIEW IF EXISTS monthly_report RESTRICT;

Materialized Views: PostgreSQL vs Oracle vs SQL Server

Materialized views store query results physically, refreshing them periodically or on demand. They differ significantly across databases. In PostgreSQL, you create a materialized view with CREATE MATERIALIZED VIEW mv_name AS SELECT ... and refresh it via REFRESH MATERIALIZED VIEW mv_name. Oracle offers similar syntax but adds advanced features like query rewrite (automatically using materialized views to speed up queries) and incremental refresh using materialized view logs. SQL Server uses indexed views: you create a view with CREATE VIEW view_name WITH SCHEMABINDING AS SELECT ... and then create a unique clustered index on it. SQL Server automatically maintains the indexed view data during DML operations, but it imposes restrictions (e.g., no outer joins, no subqueries). PostgreSQL's materialized views are simpler but require manual refresh (or a trigger). Oracle's materialized views are the most feature-rich, supporting fast refresh, complete refresh, and refresh groups. Choose based on your DBMS: PostgreSQL for simplicity, Oracle for complex refresh strategies, SQL Server for automatic maintenance with indexed views.

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_orders AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

REFRESH MATERIALIZED VIEW mv_orders;

-- Oracle
CREATE MATERIALIZED VIEW mv_orders
REFRESH FAST ON DEMAND
AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- SQL Server (Indexed View)
CREATE VIEW v_orders WITH SCHEMABINDING AS
SELECT customer_id, COUNT_BIG(*) AS order_count
FROM dbo.orders
GROUP BY customer_id;

CREATE UNIQUE CLUSTERED INDEX idx_v_orders ON v_orders(customer_id);

Updatable Views: WITH CHECK OPTION and Instead-Of Triggers

Updatable views allow INSERT, UPDATE, DELETE operations through the view. However, not all views are updatable by default. The WITH CHECK OPTION clause prevents modifications that would make rows disappear from the view. For example, a view CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1 WITH CHECK OPTION will reject any UPDATE that sets active = 0 or INSERT with active = 0. This prevents data corruption by ensuring all changes remain visible through the view. When a view is not naturally updatable (e.g., involves joins or aggregations), you can use INSTEAD OF triggers (in SQL Server, Oracle, PostgreSQL) to define custom logic. For instance, an INSTEAD OF INSERT trigger on a view that joins two tables can insert into both underlying tables. This gives you full control over how DML operations are executed. Use WITH CHECK OPTION to enforce data integrity on simple views, and INSTEAD OF triggers for complex views where direct updates are impossible.

-- WITH CHECK OPTION example
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE active = 1
WITH CHECK OPTION;

-- This will fail because active = 0 violates the view
UPDATE active_users SET active = 0 WHERE id = 1;

-- Instead-Of Trigger example (SQL Server)
CREATE VIEW order_summary AS
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

CREATE TRIGGER trg_order_summary_insert
ON order_summary
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO customers (customer_name)
    SELECT i.customer_name
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM customers WHERE customer_name = i.customer_name);

    INSERT INTO orders (order_date, customer_id)
    SELECT i.order_date, c.customer_id
    FROM inserted i
    JOIN customers c ON i.customer_name = c.customer_name;
END;

View Performance: Inline vs Materialized vs CTE

Views can be categorized by performance behavior: inline (standard) views, materialized views, and Common Table Expressions (CTEs). Inline views are virtual; each query referencing them expands the view definition, which can lead to repeated execution of complex logic. Materialized views pre-compute and store results, offering fast reads but requiring maintenance. CTEs are temporary result sets within a single query; they are not stored and are evaluated each time they are referenced (unless materialized by the optimizer). Performance-wise, materialized views are best for heavy aggregations or joins on large tables (e.g., 12M rows). Inline views are fine for simple filters but can cause performance issues if nested or used in complex queries. CTEs are useful for recursive queries or breaking down complex logic, but they may be materialized multiple times if referenced multiple times in the same query (use MATERIALIZE hint in PostgreSQL or OPTION (RECOMPILE) in SQL Server to control behavior). For dashboards, prefer materialized views for pre-aggregated data, inline views for simple security layers, and CTEs for ad-hoc analysis. Always test with realistic data volumes.

-- Inline view (expands each time)
SELECT * FROM (
    SELECT customer_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY customer_id
) AS order_counts
WHERE cnt > 10;

-- Materialized view (pre-computed)
CREATE MATERIALIZED VIEW mv_order_counts AS
SELECT customer_id, COUNT(*) AS cnt
FROM orders
GROUP BY customer_id;

SELECT * FROM mv_order_counts WHERE cnt > 10;

-- CTE (evaluated once if referenced once, but may be re-evaluated if referenced multiple times)
WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM order_counts WHERE cnt > 10;