SQL Virtual Table

Introduction to SQL Virtual Table

The virtual tables in SQL are the logical structures and entities that help us adding functionality that simplifies the complex query’s resultset or part of the contents of the table to be used as a separate table that actually doesn’t exist on the physical memory but resembles it in working. The virtual tables are also referred to as the views that have advantages of providing security to the data which is allowed to access, create a table like the logical structure of the complex query, and using it as subqueries in more than one query which ultimately simplifies querying and providing an abstraction to the users.

In this article, we will learn about the types of virtual tables that view, their implementation with the help of certain examples, and also the advantages and disadvantages of using them.

What are Virtual Tables?

Virtual tables are views that are logical structures that are created based on one or more tables and applying complex functions and clauses like GROUP BY on an optional basis. This is nothing but the query statement that is stored in the data dictionary. This is often termed as a special version of the tables. They normally do not consume any physical memory in the database to store its contents whenever we do the query on a virtual table it internally executes the query that is defined while creating the view and then places that resultset in the main query where that view is used. Hence, in one way it helps us to store the complex queries and then simply the usage of them in subqueries of other queries.

There are often situations where we only want to allow access on part of table data that satisfies certain constraints to some users. For these security reasons what we can do is define a view that is a virtual table for that particular table satisfying those particular constraints and granting the privilege of that virtual table to that user instead of the original table. Other than this, virtual tables also prove helpful while we have to retrieve only the summarized data from the table and add abstraction over the original tables by defining the views that involve group statements. Also, note that if changes are made in any records of the simple views do affect the table automatically.

Types of Virtual Tables

The views i.e virtual tables are categorized into four types that are listed as below:

• Simple View
• Complex View
• Inline View
• Materialized View

Let us understand each type of virtual table in detail –

1. Simple View

It is the view that is defined based on the contents of a single table and that does not involve usage of any complex functions or group by statement. We can perform Data Manipulation Language commands like insert, update and delete on the records of the simple virtual table which automatically affects the original table. Consider an example where there is an existing table named educba_articles that has the structure and contents as shown in the output of the following query statement –

SELECT * FROM `educba_learning`;

The execution of the above query statement gives an output which is as shown below –

Now, we will create a simple view that has the session value ranging from 500 to 750 and name it as optimal_packs and having column values of the only subject as the topic and session count in it using the following query statement –

CREATE VIEW optimal_packs AS
SELECT SUBJECT AS topic, sessions AS "session count"
FROM educba_learning
WHERE sessions BETWEEN 500 AND 750;

The execution of the above query statement gives an output which is as shown below –

Let us select the records from the virtual table and check its contents –

SELECT * FROM `optimal_packs`;

The execution of the above query statement gives an output which is as shown below –

2. Complex Vitual Tables

These are the views that are defined based on more than one table or even on a single table that involves usage of complex functions such as aggregate functions and group by statement. Note that these types of virtual tables are not allowed for manipulations by Data manipulation Language commands. This is mostly used for abstraction, providing summarized data, and using this in subqueries in multiple queries. Let us consider one example,

SELECT * FROM `educba_experts`;

The execution of the above query statement gives output which is as shown below.

Now, we will create a complex view that has the joining_date and count of members joined on each day and name it as joining_count using the following query statement.

CREATE VIEW joining_count AS
SELECT DATE(joining_date_time) AS joining, COUNT(id) AS "member count"
FROM educba_experts
GROUP BY DATE(joining_date_time);

The execution of the above query statement gives an output which is as shown below.

SELECT * FROM `joining_count`;

The execution of the above query statement gives an output which is as shown below.

3. Inline View

Inline views are the terms given to virtual tables that are most often used in subqueries. This is created for the sole purpose of having a temporary table that could be referenced and used further and is even referred to as duplicates of the original table as the contents are not actually modified and the data that is fetched from this virtual tables are not stored anywhere physically in memory.

4. Materialized Virtual Table

This is the views that are even stored in a database that is a copy of the original table is created and stored in the memory. These are logical structures that are physically stored. This is also called replications and the main difficulty that arises while using this type of virtual table is that the changes made in replications need to be updated in the original table.

Conclusion

Here we discussed what are SQL Virtual Tables and how we can make the use of virtual tables for various reasons like using them for abstraction purposes, security reasons, summarization, making the queries by using them in subqueries. However, disadvantages involve changing the view when original table structures are changed and computation time as every time view is used internally a query is fired to generate the result set and it is not like the actual table whose result is stored.

Recommended Articles

We hope that this EDUCBA information on “SQL Virtual Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Limit Offset
2. MySQL NOW
3. MySQL FETCH
4. MySQL Update Join