SQL Reference - VIRTUAL

VIRTUAL

VIRTUAL in SQL is a keyword used in the context of creating virtual tables or virtual columns. A virtual column is not stored in the database but is computed in real-time when a query is made. Use of VIRTUAL columns can assist in simplifying queries, contribute to space conservation, and aid with performance optimization under certain conditions.

Example

CREATE TABLE employee (
    id INT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

INSERT INTO employee (id, first_name, last_name)
VALUES (1, 'John', 'Doe');

SELECT * FROM employee;

Output

+------+------------+-----------+--------------+
| id   | first_name | last_name | full_name    |
+------+------------+-----------+--------------+
| 1    | John       | Doe       | John Doe     |
+------+------------+-----------+--------------+

Explanation

The above example demonstrates the usage of VIRTUAL in MySQL. It creates a VIRTUAL column full_name in the employee table. This column is generated by concatenating first_name and last_name. When a new record is inserted into the table, MySQL automatically calculates and stores the value for the full_name column.

Example

CREATE TABLE Students (
    StudentID INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Age INTEGER,
    GradeLevel INTEGER,
    GPA REAL,
    TotalScore AS (GPA * GradeLevel) VIRTUAL
);

Output

Table Students created.

Explanation

In the above SQL code, a table called Students is created with columns for StudentID, FirstName, LastName, Age, GradeLevel, and GPA. In addition, there is a VIRTUAL column, TotalScore, which is calculated on the fly and doesn’t store any data permanently. It’s computed as GPA multiplied by GradeLevel each time data is read out of the table.