Why should we use Views with Relational Databases

By Naveena Chandran

Photo by fabio on Unsplash

What are database views and why should we have them in our database?

A database view is a virtual table based on the result-set of a query statement. A view contains rows and columns, just like a real table. Views are stored in a database as named queries and are used for frequently used complex queries. The fields in a view are fields from one or more real tables in the database. We have to run a query first to get a view. This makes querying a view slower than querying directly from a table.

So naturally the question arises, Why don’t we use queries on just the tables? When should we decide to create a view in our database?

In certain situations views can provide many advantages over tables. Views can represent a subset of the data contained in a table. Views can limit the degree of exposure of the underlying tables to the outer world. A given user may have permission to query the view, while denied access to the rest of the database table. But since we have to run a query to get the view, querying from a view is slower and that is a price we pay. But even with that disadvantage on speed in some cases it is a good idea to use views.

When should we use Views?

Here are a few common use cases

Manage permissions – Hide some of the columns or rows from a table. Views can be very helpful if you want a college grades app to only read data about your class students (“where subject=’AB001’) and only expose columns that do not include identifiable information (select grades and not student_id , first_name,last_name etc.)

Simplify complex queries. If you need to join several tables and deploy some business logic in a query, and then reuse these joins and business logic across multiple queries, a view can be helpful as it allows you to write the logic once and use repeatedly. As an example join your sales, profit, products, stores and staff tables to flatten your structure in a view and read from this view.

Mask connections to remote databases. In some environments we need to read from remote databases like Oracle database links or Microsoft SQL linked servers. A view can mask this from users/developers who won’t even know they are reading from a remote source.

Customize or beautify table and column names. Some systems have table and columns names that are hard to remember and use. You may want to create views that read the same data but rename column into nicer, readable names.

Union / Union all between different tables with a similar structure. This is a specific case of simplifying complex queries – if you are reading from multiple tables with the same structure, union and union all can help simplify your query and allow you to read from one view instead of several tables.

In summary, when we need to limit the exposure of data or customize data for different users where speed is not the top priority database views are generally used.