Views

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.


              1234567891011121314151617181920212223242526
            
-- Create a view that shows account details along with their latest action
CREATE OR REPLACE VIEW account_activity_view AS
SELECT
    ba.account_number,               -- Select the account number from the bankaccounts table
    ba.account_holder,               -- Select the account holder's name from the bankaccounts table
    ba.balance,                      -- Select the balance from the bankaccounts table
    ul.action AS latest_action,      -- Select the latest action from the userlogs table (alias as latest_action)
    ul.timestamp AS latest_action_time -- Select the timestamp of the latest action (alias as latest_action_time)
FROM
    BankAccounts ba                 -- Main table: bankaccounts
LEFT JOIN
    (
        -- Subquery to get the most recent action for each account
        SELECT DISTINCT ON (account_number)
            account_number,          -- Select the account number
            action,                 -- Select the action
            timestamp               -- Select the timestamp
        FROM
            UserLogs
        ORDER BY
            account_number,          -- Order by account number
            timestamp DESC          -- Order by timestamp in descending order to get the latest action
    ) ul ON ba.account_number = ul.account_number; -- Join the subquery result with bankaccounts on account number

-- Get data from the view
SELECT * FROM account_activity_view;

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.

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 3. Розділ 7