SQL Views

Posted in /   /  

SQL Views
vinaykhatri

Vinay Khatri
Last updated on December 10, 2024

    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_1column_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_1column_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:

    Leave a Comment on this Post

    0 Comments