Views

Advanced Techniques in SQL

In the previous chapter, we mentioned an object named view when discussing user privileges. Now let's consider this object in more detail.

A view in SQL is a virtual table that is based on the result-set of an SQL query. It contains rows and columns just like a real table, and the fields in a view are fields from one or more real tables in the database.
Views are used for several reasons, including simplifying complex queries, enhancing security by restricting access to specific data, and presenting data in a specific format.

Key Features of Views

Virtual Table: A view does not store data physically. It derives its data from the tables on which it is based (called base tables);
Simplify Complex Queries: Views can simplify complex SQL queries by encapsulating them into a single view. This makes it easier to work with complex joins and aggregations;
Security: Views can restrict access to specific rows or columns of data, enhancing security. Users can be granted access to the view without granting them access to the underlying base tables;
Consistency: Views can present a consistent, unchanged interface even when the underlying database schema changes.

Implementation

We will create a view called account_activity_view based on our BankAccounts and UserLogs tables that shows the account number, account holder's name, balance, and the most recent action taken on the account.

Query Description

CREATE VIEW account_activity_view AS
This statement creates a view named account_activity_view that combines information from the bankaccounts table with the latest activity from the userlogs table. It provides an overview of account holders, their current balance, and their latest action.
Main SELECT Clause:
- ba.accountnumber: Selects the account number from the bankaccounts table.
- ba.accountholder: Selects the account holder's name from the bankaccounts table.
- ba.balance: Selects the balance from the bankaccounts table.
- ul.action AS latest_action: Selects the latest action from the userlogs table and aliases it as latest_action.
- ul.timestamp AS latest_action_time: Selects the timestamp of the latest action and aliases it as latest_action_time.
FROM Clause:
- bankaccounts ba: The main table is bankaccounts, and it's aliased as ba.
LEFT JOIN Clause:
- The subquery fetches the most recent action for each account.
- SELECT DISTINCT ON (accountnumber): Ensures distinct account numbers, selecting only the most recent entry per account.
- ORDER BY accountnumber, timestamp DESC: Orders the entries by account number and then by timestamp in descending order to get the latest action.
Join Condition:
- ul ON ba.accountnumber = ul.accountnumber: Joins the result of the subquery with the bankaccounts table based on the account number.

Now you can utilize this view in other queries without recreating the complex query with joins by simply using created view as an ordinary table.

Note
Although we can use the view like a regular table, it isn't actually a physical table! The data in the view isn't stored on the disk. Instead, whenever we use the view, its query is executed, and the resulting data is used in the main query.

Everything was clear?

Section 3. Chapter 7