EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL Virtual Table
 

SQL Virtual Table

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated June 30, 2023

SQL Virtual Table

 

 

Introduction to SQL Virtual Table

Virtual tables in SQL, also known as views, are logical structures and entities that assist in adding functionality to simplify the result set of complex queries or a portion of table contents. They act as separate tables that do not physically exist in memory but simulate the working of actual tables. 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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

What are Virtual Tables?

There are often situations where we only want to allow access to part of table data that satisfies certain constraints to some users. For these security reasons, we can 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 when retrieving only the summarized data from the table and adding 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, they do affect the table automatically.

Types of Virtual Tables

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

Types of Virtual Tables

  • 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 the 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 that is as shown below –

SQL Virtual Table-1.1

Now, we will create a simple view that has the session value ranging from 500 to 750 and name it optimal_packs and have 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 that is as shown below –

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

SQL Virtual Table-1.2

SELECT * FROM `optimal_packs`;

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

SQL Virtual Table-1.3

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 the usage of complex functions such as aggregate functions and group by statement. Note that these types of virtual tables are not allowed for manipulation 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 an output which is shown below.

Output-1.4

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 that is shown below.

Output-1.5

SELECT * FROM `joining_count`;

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

Output-1.6

3. Inline View

Inline views are virtual tables that are commonly used in subqueries. They are created with the purpose of providing a temporary table that can be referenced and utilized further within a query. Inline views are sometimes referred to as duplicates of the original table because they contain the same data, but the contents of the original table are not actually modified.

4. Materialized Virtual Table

Views in SQL do not store a copy of the original table in memory. Instead, they are logical structures that are defined by queries to retrieve data from underlying tables or views when accessed. This means that views are not physically stored but dynamically generated based on the query definition.

Conclusion

Here we discussed what SQL Virtual Tables are and how we can use virtual tables for various reasons like using them for abstraction purposes, security reasons, summarization, and making queries by using them in subqueries. However, there are some disadvantages associated with views in SQL. One disadvantage is that if the original table structures are modified, the view may need to be updated accordingly. Changes in column names, data types, or table relationships may require modifying the view definition to ensure its accuracy and compatibility with the new table structure.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW