SQL Views

A VIEW is an SQL statement which is used to create virtual tables, although it is just a SQL statement like SELECT, UPDATE, etc. but it holds the data like an actual table. VIEWS provide an alternative way to write a complex query, for instance, if there is a complex query you need to use often when you deal with your database which includes table JOINS, UNION operators with multiple conditions, then instead of writing that query, again and again, you can Just create a Virtual table using VIEW CREATE command and save that query data in a virtual table.

SQL Views

VIEW also bring security to a database, for instance in your database if there is a table which contains public as well as confidential data, then Using the VIEW  command you can create a virtual table which holds only the public detail and you can shear that virtual table with other developers.

CREATE VIEW

The VIEW statement is just a result table form by any SQL statement, and like a SQL table, we can also perform all the SQL clause on the VIEW table. Syntax

CREATE VIEW view_name AS
SELECT column_name_1, column_name_2, ...
FROM table_name
WHERE condition;

<Note> A VIEW table can be formed using single or multiple tables. Example Sample Table Students

+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  | NULL | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

Library

+---------+-------------------+------------+
| Book_ID | Book_Name         | Student_ID |
+---------+-------------------+------------+
|    1124 | One Hundred years |          1 |
|    1104 | The Great Escape  |          2 |
|    1209 | Beloved           |          6 |
|    1111 | Hollow            |          4 |
|    2351 | Invisible Man     |          3 |
|    1034 | A Passage         |          2 |
|    1211 | Hero              |          6 |
|    1188 | Your Name         |          5 |
|    1211 | Hero              |          6 |
|    1000 | My Hope           |          8 |
|    1000 | Go Away           |         10 |
+---------+-------------------+------------+

Query: Create a Virtual Table LibaryRent which holds the Studnet ID, name and the book they have rented from the library.

CREATE VIEW LibraryRent AS
SELECT id, name, Book_Name
FROM Students JOIN Library
ON Students.id = Library.Student_Id
ORDER BY id;

Now Like a table, we can query the virtual table LibraryRent and see its data.

SELECT * FROM LibraryRent;

Output

+------+--------+-------------------+
| id   | name   | Book_Name         |
+------+--------+-------------------+
|    1 | Luffy  | One Hundred years |
|    2 | Naruto | A Passage         |
|    2 | Naruto | The Great Escape  |
|    3 | Zoro   | Invisible Man     |
|    4 | Sanji  | Hollow            |
|    5 | Nami   | Your Name         |
|    6 | Robin  | Hero              |
|    6 | Robin  | Hero              |
|    6 | Robin  | Beloved           |
+------+--------+-------------------+

SQL Updating a View

Like the normal table of Database, we can also Update the VIEW virtual table, but in order to update a view table, we need to follow these conditions.

• There should not be any DISTINCT keyword, set functions, set operator and Order BY clause in the SELECT statement while updating a view table.
• There should be no GROUP BY or HAVING clause.

To update a View table we can use the CREATE OR REPLACE VIEW command. Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column1_name_1, column_name_2,...
FROM table_name
WHERE condition;

Example

CREATE OR REPLACE VIEW LibraryRent AS
SELECT id, name,age, Book_Name
FROM Students JOIN Library
ON Students.id = Library.Student_Id
ORDER BY id;

Verify the view table LibraryRent

SELECT * from LibraryRent;

Output

+------+--------+------+-------------------+
| id   | name   | age  | Book_Name         |
+------+--------+------+-------------------+
|    1 | Luffy  |   16 | One Hundred years |
|    2 | Naruto |   18 | A Passage         |
|    2 | Naruto |   18 | The Great Escape  |
|    3 | Zoro   |   20 | Invisible Man     |
|    4 | Sanji  |   21 | Hollow            |
|    5 | Nami   |   17 | Your Name         |
|    6 | Robin  | NULL | Hero              |
|    6 | Robin  | NULL | Hero              |
|    6 | Robin  | NULL | Beloved           |
+------+--------+------+-------------------+

The CREATE OR REPLACE command simply create a new view if there is a complete change in the Select statement or it just replaces some data sets if there are not too many changes in the SELECT statement.

Delete the VIEW

If you do not require a view in the database then simply using the DROP command we can delete the view. Syntax

DROP VIEW view_name;

Example Query: Delete the View LibraryRent

DROP VIEW LibraryRent;

Summary

• VIEW provides an alternative and quick way to write a complex query.
• VIEW tables do not occupy memory in the table, they are just a simple statement.
• Like a Normal table, we can perform all the operations on the VIEW table.
• To create a view we can use the CREATE VIEW command followed by the SELECT statement.
• To DELETE a view we can simply use the DROP Command.

People are also reading:

• SQL Indexes
• SQL NULL Values
• Constraints in SQL
• SQL SORTING Results
• SQL Transaction
• Distinct Keyword in SQL
• ORDER BY Clause in SQL
• SQL TOP, LIMIT or ROWNUM Clause
• SQL AND and OR Conjunctive Operators
• SELECT Query in SQL