Views (Virtual Tables) in SQL

Types of Views

• Simple View: A view based on only a single table, which doesn't contain GROUP BY clause and any functions.

• Complex View: A view based on multiple tables, which contain GROUP BY clause and functions.

• Inline View: A view based on a subquery in FROM Clause, that subquery creates a temporary table and simplifies the complex query.

• Materialized View: A view that stores the definition as well as data. It creates replicas of data by storing it physically.

Simple View

Complex View

Inline View

An inline view is a SELECT statement in the FROM clause of another SELECT statement to create a temporary table that could be referenced by the SELECT statement. Inline views are utilized for writing complex SQL queries without join and subqueries operations. This is called a temporary table because a duplicate copy of the data returned by the stored subquery wasn't stored in the database. In Oracle and in the Postgres community, this temporary table is called an inline view. It is referred to as a subselect.

Materialized View

Materialized view replicates the retrieved data physically. This replicated data can be reused without executing the view again. This type of view is also known as "SNAPSHOTS". Materialized view reduce the processing time to regenerate the whole data. It helps remote users to replicate data locally and improve query performance. The challenging part here is to synchronize the changes in materialized views underlying tables.

Comparison Between View and Materialized View

Creating and Dropping a view in SQL

View can be created and replaced using CREATE VIEW and REPLACE VIEW.

View can be deleted using DROP VIEW command.

Pros and Cons of Views

Views can be utilized as a subset of actual data to perform certain operations. It helps us to provide an abstraction to various users or hide the complexity for users who are accessing data from the table. For example, a user has permission to access particular columns of data rather than the whole table. It can help us to simplify complex queries into a simpler one. It also simplifies data access from multiple joined tables. It can be used as aggregated tables using group by operations. Views can be used for security purposes or can add extra value from the security point of view. It does not hold any space because it only has the definition in the data dictionary, not the copy of actual data.

Besides the lots of advantages, views also have some disadvantages such as base table structure dependency, computation time, and restrictions Views have a dependency on the table structure. If you change the table structure, then you have to change the view definition associated with it.

Conclusion

Congratulations, you have made it to the end of this tutorial!

In this tutorial, you have covered a lot of details about Views. You have learned what Views are, types of Views, Simple View, Complex View, Inline View, Materialized View, Syntax for creating and dropping views and pros and cons of Views.

Now utilize Views concept to analyze your own datasets. Thanks for reading this tutorial!

If you would like to learn more about SQL, take DataCamp's Introduction to Relational Databases in SQL course.