View in Relational Database

Simply put, Views are stored queries that when invoked produce some results.

Proper use of views in relational databases is a key aspect of good SQL database design. In this article, we will take a deep dive into the topic of Views. We will understand their utilities and look at some of their common usage.

What are Views?

A View in a relational database is referred to as the result set (set of rows) of a stored query. A view can be treated similar to any other database object (for example, table).

Let us take an example.

You can follow along using a standalone Postgres server on your PC or use this online playground provided by CrunchyData.

Starting from an empty database, we will create a table named student. This table will hold details about students of some university. To keep things simple, our table will only hold the following details —

• ID: Number that uniquely identifies a student
• Full Name
• Phone Number: Text field holding student phone numbers
• GPA
• Age

postgres=# CREATE TABLE student ( id bigint, 
                                  full_name text, 
                                  phone_number text, 
                                  gpa int, 
                                  age int
);
CREATE TABLE

Consider a use case where a number of database user would frequently want to select the ID and the GPA of students. To achieve this, each user will have to independently write a query like -

SELECT id, gpa FROM student WHERE ...;

While the ‘WHERE’ portion of the query may change from user to user, the columns to select and the table to select from remains the same. Moreover, if any of the queried columns happen to be renamed, all of its users would have to update their queries.

Creating a view would make the things a lot simple and here is how the database administrator will achieve it.

postgres=# CREATE VIEW student_id_gpa AS SELECT id AS id, gpa AS gpa FROM student;
CREATE VIEW

Now, the users can rely on the constant interface that student_id_gpa view provides without worrying about any changes to the underlying table.

SELECT id, gpa FROM student_id_gpa WHERE ...;

Use Cases for Views

Views can be useful in many cases. Let us deep dive into some of the major ones.
★ Views can hide complexity
Parts of a complex query can be broken down and moved behind views. This allows for simpler and more readable SQL queries. It also helps in better management and debugging.

A query that brings data from multiple tables, requiring several joins, can be moved behind a view for making it easier to follow and understand.

★ Views can help implement ACLs on a table
With views, a user’s access to a table’s rows and columns can be restricted. A view with only permissible parts of the table can be created. User can then be given access to use the view but not the actual table.

Let us see this in action. First we will need to create a new user and give it login permission.

postgres=# CREATE ROLE restricted_user; 
CREATE ROLE

postgres=# GRANT CONNECT ON DATABASE postgres TO restricted_user;
GRANT

We want the ‘restricted user’ to be able to see the name and ID of students who are below the age of 21. Nothing more, Nothing less!

We create a view for this and give permission to the ‘restricted user’.

postgres=# CREATE VIEW student_below_21 AS SELECT id AS id, full_name AS full_name FROM student WHERE age < 21;
CREATE VIEW

postgres=# GRANT SELECT ON student_below_21 TO restricted_user;
GRANT

Now, the new user can only view those parts of the student table that our view allows!

postgres=> SELECT * FROM student_below_21;
 id | full_name 
----+-----------
  3 | Adam
(1 row)

postgres=> SELECT * FROM student;
ERROR:  permission denied for table student

★ Views can simplify supporting legacy code or schema evolution
Often the schema of a table changes over course of time. Breaking changes in schema would break a lot of code. Views can be useful in performing incremental updates to the database schema.

The original table can be replaced with a view of same name. The original table can then be split into several tables and get their schema evolved independently! This keeps the legacy code functional which would now start using the view.

How are Views stored in Databases?

Databases do not store the result set of a view, they store the queries. This makes views space efficient. A large number of views can be created without worrying about the space taken on the database.

All the views (in Postgres) are kept in a table named views inside information_schema. Here is how you can view it.

SELECT * FROM information_schema.views;

Starting Postgres 9.3, materialised views are supported. They work like normal views but persist the result set in a database table!

Performance?

Performance is not an issue with Views. They behave like any other SQL query and it is upto the query planner to optimise database accesses. Even with added conditions and joins, views tends to perform at par with database tables!

Views in RDBMS are powerful tools that offer a convenient way to present data, simplify complex queries, and enhance security. Having a strong understanding of this concept allows us to make more informed design decisions.

As technology evolves, the strategic use of views will continue to be a fundamental aspect of relational database management, shaping the way we interact with and harness the power of data!