Views

What Is a View?

A view is a saved SELECT query that you can treat like a table. Think of it as a window into your data — you define what you want to see through that window once, give it a name, and then anyone can look through it without needing to know how it's built.

The key thing to understand: a regular view does not store any data. Every time you query a view, the database runs the underlying SELECT query and returns the results. The view is just a named, reusable query definition.

┌─────────────────────────────────────────────────────┐
│                  Database                           │
│                                                     │
│  ┌─────────────┐    ┌──────────────────────────┐   │
│  │  employees  │    │  dept_salary_summary      │   │
│  │  (table)    │───▶│  (view — saved query)     │   │
│  └─────────────┘    └──────────────────────────┘   │
│  ┌─────────────┐              │                     │
│  │ departments │──────────────┘                     │
│  │  (table)    │                                    │
│  └─────────────┘                                    │
└─────────────────────────────────────────────────────┘

The view dept_salary_summary is defined as a SELECT joining employees and departments. When you query the view, SQL runs that JOIN automatically.

Why Use Views?

Views solve several real problems:

1. Security — Hide Sensitive Columns

You can grant users access to a view while keeping sensitive columns out of reach:

-- The employees table has sensitive columns: salary, ssn, bank_account
-- Create a view that only exposes safe columns:
CREATE VIEW employee_directory AS
SELECT employee_id, first_name, last_name, email, phone_number, department_id
FROM   employees;
-- salary, ssn, bank_account are not included

-- Grant HR assistants access to the view, NOT the base table:
GRANT SELECT ON employee_directory TO hr_assistant_role;
-- They can query employee_directory but cannot see salaries

2. Simplify Complex Queries

Wrap a complex multi-table join in a view so users can query it simply:

-- Without a view, every developer must write this 4-table join:
SELECT e.first_name, e.last_name, d.department_name,
       j.job_title, l.city
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
JOIN   jobs j        ON e.job_id        = j.job_id
JOIN   locations l   ON d.location_id   = l.location_id;

-- With a view, it's just:
SELECT first_name, last_name, department_name, job_title, city
FROM   employee_details;   -- view encapsulates the complexity

3. Consistent Interface

When the underlying table structure changes, you can update the view definition without changing every application that uses it:

-- Old structure: full_name as one column
-- New structure: first_name + last_name as two columns
-- The view can present the old interface to legacy applications:
CREATE OR REPLACE VIEW employee_legacy AS
SELECT employee_id,
       first_name || ' ' || last_name AS full_name,  -- simulate old column
       salary
FROM   employees;

4. Reuse Across Queries

Define a complex subquery once as a view, then reference it in many different queries.

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM   base_table
WHERE  condition;

Simple View Example

-- View: employees earning above the company average salary
CREATE VIEW high_earners AS
SELECT employee_id,
       first_name,
       last_name,
       salary,
       department_id
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

Complex View Example (Multi-Table JOIN)

-- View: comprehensive employee information from 4 tables
CREATE VIEW employee_details AS
SELECT e.employee_id,
       e.first_name,
       e.last_name,
       e.salary,
       e.hire_date,
       d.department_name,
       j.job_title,
       l.city,
       l.country_id
FROM   employees   e
JOIN   departments d ON e.department_id = d.department_id
JOIN   jobs        j ON e.job_id        = j.job_id
JOIN   locations   l ON d.location_id   = l.location_id;

Querying a View

Once created, a view is queried exactly like a table:

-- Query the view like any regular table
SELECT *
FROM   high_earners
ORDER BY salary DESC;

-- Filter the view
SELECT first_name, last_name, salary
FROM   high_earners
WHERE  department_id = 80;

-- Aggregate over the view
SELECT COUNT(*) AS high_earner_count,
       AVG(salary) AS avg_high_salary
FROM   high_earners;

-- Join a view to a table
SELECT h.first_name, h.last_name, h.salary, d.department_name
FROM   high_earners h
JOIN   departments d ON h.department_id = d.department_id;

The database transparently runs the view's underlying SELECT as a subquery and returns the results.

CREATE OR REPLACE VIEW

To modify an existing view's definition without dropping and recreating it:

-- Replace the view definition (preserves grants)
CREATE OR REPLACE VIEW high_earners AS
SELECT employee_id,
       first_name,
       last_name,
       salary,
       department_id,
       hire_date            -- new column added
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;       -- added default sort

CREATE OR REPLACE is safer than DROP + CREATE because it preserves any GRANT permissions that were given on the view.

DROP VIEW

-- Remove a view (the underlying tables are NOT affected)
DROP VIEW high_earners;

-- Oracle: include IF EXISTS to avoid error if view doesn't exist
-- (Standard SQL / PostgreSQL)
DROP VIEW IF EXISTS high_earners;

Simple vs Complex Views

Simple View

A simple view is based on a single table with no aggregation, no DISTINCT, and no GROUP BY. Simple views are generally updatable — you can INSERT, UPDATE, and DELETE through them.

-- Simple view: single table, no grouping or aggregation
CREATE VIEW it_employees AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM   employees
WHERE  department_id = 60;
-- This is updatable: you can INSERT/UPDATE/DELETE through it

Complex View

A complex view involves JOINs, GROUP BY, DISTINCT, aggregate functions, or UNION. Complex views are generally not updatable directly.

-- Complex view: multi-table join + aggregation
CREATE VIEW dept_summary AS
SELECT d.department_name,
       COUNT(e.employee_id)  AS headcount,
       AVG(e.salary)         AS avg_salary,
       MAX(e.salary)         AS max_salary
FROM   departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
-- NOT directly updatable — involves JOIN and GROUP BY

Updatable Views — Reading and Writing Through a View

A view is updatable if the database can translate a change to the view back to a change in the base table. This requires:

-- it_employees view (simple, single table) IS updatable:
-- Update through the view:
UPDATE it_employees
SET    salary = salary * 1.05
WHERE  employee_id = 103;
-- This actually runs: UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 103

-- Insert through the view:
INSERT INTO it_employees (employee_id, first_name, last_name, salary, hire_date)
VALUES (210, 'New', 'Employee', 65000, SYSDATE);
-- This inserts into the employees table with department_id = 60 NOT automatically set!
-- (department_id is in the WHERE of the view, not in the column list)

WITH CHECK OPTION

WITH CHECK OPTION prevents INSERT and UPDATE operations through a view from creating rows that wouldn't be visible through that view. It enforces the view's WHERE condition on writes.

-- View with CHECK OPTION: any row inserted or updated must satisfy WHERE
CREATE VIEW it_employees AS
SELECT employee_id, first_name, last_name, salary, department_id, hire_date
FROM   employees
WHERE  department_id = 60
WITH CHECK OPTION;

-- This INSERT succeeds — department_id = 60 satisfies the view's WHERE:
INSERT INTO it_employees (employee_id, first_name, last_name, salary, department_id, hire_date)
VALUES (210, 'New', 'Employee', 65000, 60, SYSDATE);  -- OK ✓

-- This INSERT fails — department_id = 80 violates the view's WHERE:
INSERT INTO it_employees (employee_id, first_name, last_name, salary, department_id, hire_date)
VALUES (211, 'Wrong', 'Department', 65000, 80, SYSDATE);
-- ORA-01402: view WITH CHECK OPTION where-clause violation

-- This UPDATE fails — would move the row out of the view:
UPDATE it_employees
SET    department_id = 80
WHERE  employee_id = 103;
-- ORA-01402: view WITH CHECK OPTION where-clause violation

Views for Security — Column-Level and Row-Level

Column-Level Security (Hide Sensitive Columns)

-- The payroll table has columns: employee_id, salary, bonus, bank_account, tax_id
-- Create a restricted view for non-finance users:
CREATE VIEW employee_public_info AS
SELECT employee_id, first_name, last_name, department_id, hire_date
FROM   employees;
-- salary, commission_pct are excluded

-- Grant access to the view only, not the base table
GRANT SELECT ON employee_public_info TO reporting_user;
REVOKE SELECT ON employees FROM reporting_user;  -- ensure no direct access

Row-Level Security (Filter Rows by User)

-- Each manager can only see their own team's employees
CREATE VIEW my_team AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM   employees
WHERE  manager_id = (
    SELECT employee_id
    FROM   employees
    WHERE  email = USER   -- USER = current Oracle session username
);
-- Each manager sees only rows where they are listed as manager_id

Views and Schema Changes

When the underlying base table changes (column added, renamed, or dropped), views that reference those columns may break.

-- Original table has: employee_id, first_name, last_name, salary
CREATE VIEW my_view AS
SELECT employee_id, first_name || ' ' || last_name AS full_name, salary
FROM   employees;
-- View works fine

-- Now someone renames last_name to surname:
ALTER TABLE employees RENAME COLUMN last_name TO surname;

-- Querying the view now fails:
SELECT * FROM my_view;
-- ORA-00904: "LAST_NAME": invalid identifier

-- Oracle: find invalid views
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_type = 'VIEW'
AND    status = 'INVALID';

-- Recompile an invalid view:
ALTER VIEW my_view COMPILE;

Inline Views (Subquery in FROM Clause)

An inline view is an unnamed view — a subquery used directly in the FROM clause of another query. It exists only for the duration of that one query:

-- Inline view: find employees earning more than their department average
SELECT outer_q.first_name, outer_q.last_name, outer_q.salary, outer_q.dept_avg
FROM (
    -- This subquery is the "inline view"
    SELECT e.employee_id,
           e.first_name,
           e.last_name,
           e.salary,
           e.department_id,
           AVG(e.salary) OVER (PARTITION BY e.department_id) AS dept_avg
    FROM   employees e
) outer_q
WHERE outer_q.salary > outer_q.dept_avg
ORDER BY outer_q.department_id;

Inline views are useful for multi-step calculations where you need to filter on an aggregated value. They're similar to CTEs but are embedded in the query rather than defined with WITH.

Materialized Views — Storing the Results

A materialized view (MVIEW) is different from a regular view: it stores the actual query results physically, like a table. When you query a materialized view, you read pre-computed data — no query execution needed. This makes them extremely fast for complex, slow queries.

Regular View:        Query → runs live → returns results
Materialized View:   Query → reads stored snapshot → returns results (fast!)
                             (snapshot is refreshed on a schedule or manually)

When to Use Materialized Views

CREATE MATERIALIZED VIEW (Oracle)

-- Oracle: create a materialized view of department salary summaries
CREATE MATERIALIZED VIEW dept_salary_mv
BUILD IMMEDIATE           -- populate data immediately when created
REFRESH COMPLETE          -- full refresh (re-runs entire query)
ON DEMAND                 -- refresh manually when requested
AS
SELECT d.department_id,
       d.department_name,
       COUNT(e.employee_id) AS headcount,
       AVG(e.salary)        AS avg_salary,
       MAX(e.salary)        AS max_salary,
       MIN(e.salary)        AS min_salary
FROM   departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

Querying a Materialized View

Query it like a regular table — no difference from the user's perspective:

-- Fast: reads pre-computed data
SELECT department_name, avg_salary, headcount
FROM   dept_salary_mv
ORDER BY avg_salary DESC;

REFRESH MATERIALIZED VIEW

The data in a materialized view gets stale as the base tables change. You must refresh it:

-- Oracle: manually refresh a materialized view
EXEC DBMS_MVIEW.REFRESH('DEPT_SALARY_MV');

-- PostgreSQL:
REFRESH MATERIALIZED VIEW dept_salary_mv;

-- PostgreSQL: refresh without locking out reads (concurrent)
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_salary_mv;

Automatic Refresh (Oracle)

-- Oracle: refresh automatically every day at midnight
CREATE MATERIALIZED VIEW dept_salary_mv
REFRESH COMPLETE
START WITH TRUNC(SYSDATE) + 1   -- first refresh: start of tomorrow
NEXT  TRUNC(SYSDATE) + 1 + 1/24 -- subsequent: every 24 hours
AS
SELECT ...;

Oracle Fast Refresh

Oracle supports fast refresh (incremental refresh), where only the changed rows are applied to the materialized view. This requires a materialized view log on the base table:

-- Step 1: create a log on the base table to track changes
CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE (employee_id, salary, department_id)
INCLUDING NEW VALUES;

-- Step 2: create the MV with FAST refresh
CREATE MATERIALIZED VIEW dept_salary_mv
REFRESH FAST ON COMMIT   -- auto-refresh whenever base table is committed
AS
SELECT department_id,
       COUNT(*) AS headcount,
       SUM(salary) AS total_salary
FROM   employees
GROUP BY department_id;

Regular View vs Materialized View

Practical View Examples

View: Department Headcount Summary

CREATE VIEW dept_headcount AS
SELECT d.department_id,
       d.department_name,
       COUNT(e.employee_id)        AS num_employees,
       ROUND(AVG(e.salary), 2)     AS avg_salary
FROM   departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

View: Top Earners Per Department

CREATE VIEW dept_top_earners AS
SELECT department_id, first_name, last_name, salary
FROM (
    SELECT department_id, first_name, last_name, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM   employees
)
WHERE rnk = 1;

Materialized View: Monthly Sales Summary

CREATE MATERIALIZED VIEW monthly_sales_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT EXTRACT(YEAR FROM order_date)  AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       product_id,
       SUM(quantity)                  AS total_units,
       SUM(amount)                    AS total_revenue
FROM   orders
GROUP BY EXTRACT(YEAR FROM order_date),
         EXTRACT(MONTH FROM order_date),
         product_id;

Common Errors

Interview Corner

CREATE OR REPLACE VIEW it_employees AS
SELECT employee_id, last_name, department_id
FROM   employees
WHERE  department_id = 60
WITH CHECK OPTION;

-- Allowed: new row stays in the IT view
INSERT INTO it_employees VALUES (999, 'Park', 60);

-- Rejected (ORA-01402): new row would not appear in the view
INSERT INTO it_employees VALUES (998, 'Lee', 80);

Related Topics

• Subqueries — inline views (subqueries in FROM) as an alternative to named views
• CTEs & Procedures — CTEs as session-scoped "views" for complex ad-hoc queries
• Indexes — materialized views can have indexes; query rewrite interacts with index plans
• Data Dictionary — USER_VIEWS, USER_MVIEWS to inspect view and MV definitions