Advanced3 min read

Creating and Using Views

Understanding Views

In a relational database, views act as virtual tables. Unlike base tables that physically store data on a disk, a view does not contain any data of its own. Instead, it simply contains a SQL query that dynamically retrieves data when the view is accessed. You can think of a view as a named query stored permanently within the database schema. When you query a view, the database engine executes the underlying SELECT statement and presents the resulting grid as if it were a standard, physical table.

Why Use Views?

There are several compelling reasons to incorporate views into your database architecture.

First, views simplify complex data manipulation. If your reporting requires you to frequently join multiple tables, apply specific filters, and calculate fields, you can encapsulate that entire SELECT statement within a single view. End-users and applications can then query the simple view directly, completely hiding the complexity of the underlying database schema.

Second, views provide an effective layer of data security. You can restrict user access to specific subsets of data. For example, you can create a view that exposes an employee's name and department but deliberately excludes sensitive columns like their salary or home address. By granting users permission to query the view rather than the base table, you enforce strict access control without duplicating data.

Third, views can be used to reformat and standardize retrieved data on the fly, ensuring that calculated metrics or concatenated strings are always evaluated consistently across all your applications.

Creating Views

To create a view, you use the CREATE VIEW statement. This requires you to specify the name of the view, followed by the AS keyword, and then the complete SELECT query that defines it.

POSTGRESQL CODE SNIPPET

CREATE VIEW ActiveCustomers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'Active';

🚀 Try in Playground

Once the view is created, you interact with it exactly as you would any regular table.

POSTGRESQL CODE SNIPPET

SELECT first_name, last_name
FROM ActiveCustomers
ORDER BY last_name;

🚀 Try in Playground

You can even build nested views, which are simply views built upon the results of other views, provided you avoid creating circular references.

Updating Views

While views are primarily used for reading data, many database systems allow you to update the underlying base tables directly through the view. These are known as updatable views.

However, updatable views come with strict limitations. A view is generally read-only if its underlying query includes aggregate functions, GROUP BY clauses, or complex joins. Furthermore, if you intend to INSERT records through an updatable view, that view must contain all the required NOT NULL columns of the underlying base table.

If you want to ensure that all data modifications made through the view strictly adhere to the view's internal filtering criteria, you can append the WITH CHECK OPTION clause during its creation. This prevents users from inserting a row that the view itself would immediately filter out.

Removing Views

If a view is no longer needed, you can cleanly remove it from the database using the DROP VIEW statement.

POSTGRESQL CODE SNIPPET

DROP VIEW ActiveCustomers;

🚀 Try in Playground

Because a view is merely a stored query, dropping a view only deletes its definition. It never deletes or alters the physical data residing in the underlying base tables.

Apply these virtual table techniques to simplify your queries and secure your data access before moving on to stored procedures.

← Previous ChapterCreating and Manipulating Tables Final Step →Enter SQL Playground