10. SQL Views

Views - Virtual Tables in SQL

Tables in relational databases have a physical existence on a storage medium: they are structured collections of data records. They consume physical space. In many application scenarios, however, only selected records or individual attributes of a table (or several linked tables) are required. Queries that provide exactly the relevant data are suitable for this purpose. Since identical queries are often reused, it is recommended to use predefined queries, so-called Views.

Concept and Key Properties

• Definition: A view is a SELECT statement stored under a name. This is somewhat comparable to a function in programming languages that returns a value, or a bookmark in a web browser.
• Storage Requirements: Apart from the definition itself, a view requires no additional storage for data; it references the underlying tables. This means that the query is stored, not the result. When the view is accessed, the query is executed and the current result set is returned.
• Up-to-date: The result set of a view always reflects the current state of the base tables.
• User Perspective: Views correspond to the external level of the three-level architecture and thus provide a task-specific view of the data. Fundamentally, an application cannot distinguish whether it is working with a view or a table.

Fig. 11.1: Two tables are connected (join) and provided as a view with selected columns. In the view, some columns of the base tables are hidden.

Source: [2]

Benefits in Modeling and Operation

• Simplification of Joins: Linked tables can be treated like a single logical table via a view. This encapsulates complex JOIN constructs and makes them reusable. One does not have to repeatedly formulate the same joins in queries.
• Reduction of Complexity: Business logic, filters, and calculations are defined centrally in the view and used consistently, e.g., “last 24 hours” views for reports or dashboards.
• Security Aspect: Views enable finer access control. Access can be restricted to selected columns and rows without changing the structure of the base tables.
• Interface Stability: Applications can be developed against views. Changes to base tables can be encapsulated behind the view, keeping interfaces more stable. This allows databases to evolve without requiring adjustments to existing applications. Applications access the view, which continues to provide the same structure and semantics, even if the underlying tables have changed.

Example

-- View that only provides active customer data and hides sensitive columns
CREATE VIEW active_customers AS
SELECT
  c.id,
  c.name,
  c.email
FROM customers AS c
WHERE c.active = TRUE;

-- Using the view like a table:
SELECT * FROM active_customers;

-- Updating via the view (possible, since it's a simple view based on a single table without aggregations):
UPDATE active_customers
SET email = '[email protected]'
WHERE id = 42;

Everyday Use in SQL

• Queries: Views are used in SELECT statements just like tables.
• Changes via Views: Depending on the database system and definition, updatable views are possible. In these cases, INSERT, UPDATE, or DELETE can be executed via the view, passing the changes through to the base tables. Restrictions (e.g., no aggregations, no DISTINCT clause) are system-specific.
• Hiding Attributes: Columns not included in a view are not available during the further use of the view; the base tables remain unchanged.
• Portability: Not all database systems support the full range of view features.

DML Operations on Views

When can a DML operation (INSERT, UPDATE, DELETE) be performed on views?

• Simple Views: If the view is based on a single table and contains no aggregations, DISTINCT, GROUP BY, HAVING, UNION, or joins, DML operations are usually possible.
• Multi-Table Views: For views based on multiple tables (joins) or containing subqueries, DML operations are often restricted or not allowed, as it may be unclear how the changes should be distributed across the underlying tables.
• Restricted Views: If the view contains filter conditions (e.g., WHERE clauses), DML operations can only be applied to rows that meet these conditions: e.g., a view that only shows active customers does not allow updates or deletes on inactive customers. This can be enforced with the WITH CHECK OPTION clause.
• System Dependency: The exact rules for DML operations on views vary between different database systems. It is important to consult the documentation of the respective system.

Guidelines for Designing Views

• Naming and Purpose: Views should be clearly named and tailored to a specific business task.
• Performance: While the definition requires little storage, the execution time depends on the underlying query (e.g., number of joins, filters, indexes).
• Security Rules: In combination with authorization systems, “least privilege” strategies can be implemented by granting access only to the respective view. For example, the base table “employees” remains protected, while the view “public_employees” provides only non-sensitive data.

Summary: Views are stored queries that provide current data from base tables, simplify working with linked tables, stabilize interfaces, and precisely control access to relevant subsets of data.

Learning Outcomes: What you should be able to do after this chapter

After completing this chapter, students should be able to:

• Define: define the term view as a virtual table.
• Describe: describe the benefits of views for modelling, security and reuse.
• Apply: create views with CREATE VIEW and use them in queries.
• Explain: explain under which conditions DML operations on views are possible.
• Evaluate: assess when the use of a view is appropriate and how it should be well designed.