SQL Views

definitions
databases
sql
views

A view is a single table derived from other tables — base tables or other views — and defined by a query rather than by stored rows. Once created it is queried exactly like a table, which makes a view the natural way to name an intermediate result, hide complexity, or expose a restricted slice of the database to a class of users — the external level of the three-schema architecture, made concrete (dbms-fundamentals). Introduced in 2026-04-13-nested-queries-views-and-generative-ai; practised in week9-tutorial-applied-class-8-nested-queries-and-views.

Virtual vs materialized

  • Virtual — the view does not physically exist on disk; it is recomputed each time it is queried, so it is always current. Think of it as a way of specifying a table you need to reference frequently, even though no such table exists.
  • Materialized — the view table is physically created, and must be updated whenever the base tables are updated.

Creating and querying a view

CREATE VIEW <view name> (<column name> {, <column name>}) AS <select statement>;

The column-name list is optional, even though the syntax above does not put it in [...]: supply it and the view’s columns take those names, omit it and they take the names of the target list — which is why a computed column needs an AS alias to have a name at all.

-- Count, gender and average salary of employees, per department
CREATE VIEW DepEmpStatus AS
SELECT   dNumber, dName, sex, COUNT(*) AS employeeNumber, AVG(salary) AS avgSalary
FROM     Department AS D
JOIN     Employee AS E ON D.dNumber = E.dNum
GROUP BY dNum, sex;

SELECT * FROM DepEmpStatus;

A view has a schema like any other relation, and is written the same way in relational-mapping-notation — the aliases given in the target list become its column names. It is listed in the same schema as the base tables it is derived from, directly under them:

Department [dNumber, dName, mgrSSN, mgrStartDate]
Employee   [ssn, name, dob, address, sex, salary, mgrSSN, dNum]

VIEW DepEmpStatus [dNumber, dName, sex, employeeNumber, avgSalary]

That placement is the point: from the query writer’s side a view is one more relation in the schema, and nothing in a SELECT distinguishes it from a stored table.

Querying a view that computes its own columns

Because the view is just a table to the queries that follow, it can be joined, filtered, grouped and aggregated over in turn — including aggregating over columns the view itself computed:

CREATE VIEW V AS SELECT a + b AS d, c FROM R;

SELECT   d, SUM(c)
FROM     V
GROUP BY d
HAVING   COUNT(*) <> 1;

Worked exercise: given R below, which tuples does the second query return?

R.a R.b R.c V.d = a+b V.c
1 1 3 2 3
1 2 3 3 3
2 1 4 3 4
2 3 5 5 5
2 4 1 6 1
3 2 4 5 4
3 3 6 6 6

Grouping V by d gives four groups — d = 2 (one row), d = 3 (c = 3, 4), d = 5 (c = 5, 4) and d = 6 (c = 1, 6). HAVING COUNT(*) <> 1 throws away the singleton group d = 2, so the answer is (3, 7), (5, 9) and (6, 7). Two things are worth taking from it: the grouping attribute d does not exist in any base table — it is created by the view and grouped over as if it were stored; and HAVING filters groups, so (2, 3) is excluded even though its underlying row is perfectly ordinary.

Why views exist

Benefit What it buys you
Simplification Hides the complexity of the underlying tables behind one name
Security Hides sensitive columns from users who should not see them
Computed columns Derived values are computed on the fly, not stored
Logical data independence Users and programs querying the view are insulated from changes to the underlying logical schema

The security case is concrete: a user granted access to DepEmpStatus but not to Department/Employee can read departmental statistics without ever seeing an individual employee’s address or salary. See database-access-control for where this sits among the course’s authorisation mechanisms — granting a privilege on a view rather than on the base table restricts which columns and which rows an account can read at the same time, which a table-level grant cannot express.

Views instead of repeated subqueries

The worked example is “find the name of the department that spends the largest sum of money on salaries”. A query that needs the same derived table twice ends up computing it twice inside itself, which is hideously ugly:

-- Hideously ugly: the per-department total is re-derived in two places
SELECT   d.dName
FROM     Department d,
         (SELECT dNum, SUM(salary) AS departmentWage
          FROM   Employee GROUP BY dNum) AS Temp
WHERE    d.dNumber = Temp.dNum
  AND    Temp.departmentWage = (
      SELECT MAX(departmentWage)
      FROM   (SELECT SUM(salary) AS departmentWage
              FROM   Employee GROUP BY dNum) AS Temp2
  );

-- Cleaner: define the intermediate result once
CREATE VIEW Temp AS
SELECT   dNum, SUM(salary) AS departmentWage
FROM     Employee
GROUP BY dNum;

SELECT *
FROM   Temp
WHERE  departmentWage IN (SELECT MAX(departmentWage) FROM Temp);

Both halves of the ugly version compute the same grouped aggregate; the view names it once and both the outer query and the MAX subquery then read that one name. (The second query selects from Temp only, so it returns the winning dNum/departmentWage pair — recovering dName needs one further join back to Department, which is the step drawn at the bottom of Figure 1.)

Figure 1: The worked view example, run on sample data: the per-department salary total is computed once into the view, and only then filtered down to the maximum. Department 99, a department with no employees, produces no row in the view at all, because the grouped join has nothing to group for it; a question phrased which department spends the most never notices, but one phrased list every department’s total would.

This is the standard way to answer “the group with the largest total” questions: a view holding one row per group with its aggregate, then a second query selecting the rows matching the extreme (sql-nested-queries covers the >= MAX(...) / >= ALL idioms that keep ties). Because a view definition is persistent, a script that recreates one should drop it first:

DROP VIEW IF EXISTS EarliestRoleGranting;

CREATE VIEW EarliestRoleGranting AS
SELECT   RoleID, MIN(TimeStamp) AS EarliestTimeStamp
FROM     RoleGranting
GROUP BY RoleID;

-- Who received each role first: join the base table back to the view
SELECT E.EmployeeID, E.FirstName, E.LastName, RG.RoleID
FROM   RoleGranting RG
JOIN   EarliestRoleGranting ERG
       ON ERG.RoleID = RG.RoleID AND ERG.EarliestTimeStamp = RG.TimeStamp
JOIN   Employee E ON E.EmployeeID = RG.EmployeeID;

That join-back pattern — aggregate in a view, then join the view to the base table on the aggregated value — is how you retrieve the rows that achieve an aggregate, which a GROUP BY alone cannot give you (sql-aggregation-and-grouping).

Updating through a view

A view has no rows of its own, so view updates must occur at the base tables — and working out which base rows an update to a view row should touch is ambiguous and difficult. Consider DepEmpStatus above: its avgSalary column is an average over many employees, so an instruction to change it names no particular salary to change, and its employeeNumber column cannot be changed at all without inventing or destroying employees.

DBMSs therefore restrict view updates to some simple views on single tables, which are the ones called updatable views. The characteristic shape is a view that selects a subset of the rows and columns of one base table, with no join, no grouping and no aggregate, so each view row corresponds to exactly one base row.

That restriction is what makes the security case in the table above practical rather than read-only: the manager of each department can be given a view of the employees of their department, and because it is a plain row-and-column slice of one table, they can access and edit through it.

Dropping views

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE];

DROP VIEW DepEmpStatus;

Dropping a view does not affect any tuples of the underlying relation — only the definition goes away. That asymmetry is the whole point of a virtual view: nothing of the data lives in it.

Views do, however, constrain what can be done to the tables under them: DROP TABLE … RESTRICT drops the table unless there is a view on it, while DROP TABLE … CASCADE drops the table and recursively drops any view referencing it (see sql-ddl).