SQL VIEW

A VIEW in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

A VIEW in SQL is a logical subset of data from one or more tables. A view is created using data fetched from some other table(s). It's more like a temporary table created with data. It is like a virtual table. You can create a view from a single table or multiple tables.

Why should we use SQL Views?

• It restricts data access and hence provides security.
• It hides data complexity that exists in a multiple-table join.
• It provides easy commands for the end-users.
• It can store complex queries.
• It can be used to rename columns without affecting the base tables.
• It provides a multiple view facility.
• An advantage of a view is that it does not take space as it stores your code, not complete tables.
• A view always shows up-to-date data.

Let's look at different examples of creating a view, displaying and inserting records of a view, deleting, updating, or dropping a view in a database.

Syntax for Creating a View

    CREATE or REPLACE VIEW view_name 
    AS
    SELECT column_name(s)
    FROM table_name(s)
    WHERE condition;

Sample Tables

Let's consider the below two tables for creating views.

EmpDetails Table

DeptDetails Table

Example: Create a View from a Single Table

The below SQL query creates a view using the EmpDetails table. The data fetched from the SELECT statement will be stored in another object called Emp_View. We can use CREATE and REPLACE statements separately, but using both together works better, as if any view with the specified name exists, this query will replace it with fresh data.

    CREATE or REPLACE VIEW Emp_View
    AS
    SELECT Emp_Name, Location FROM EmpDetails
    WHERE Emp_ID < 5;

Example: Create a View from Multiple Tables

The below SQL query creates a view named Dept_View from two tables EmpDetails and DeptDetails. In this example, we simply include multiple tables in the SELECT statement.

    CREATE VIEW Dept_View 
    AS
    SELECT EmpDetails.Emp_Name, EmpDetails.Location, DeptDetails.Dept_Name
    FROM EmpDetails, DeptDetails
    WHERE EmpDetails.Emp_Name = DeptDetails.Emp_Name;

Output:

Example: Display a View

This example shows how to display the data of a view. The given syntax is similar to fetching data from a table using a SELECT statement. The below SQL query shows the contents of Emp_View.

    SELECT * FROM Emp_View;

Output:

Example: Insert a Row in a View

The below SQL query inserts a row in the view Emp_View just like a table using INSERT INTO statement.

    INSERT INTO Emp_View VALUES("Samuel","Germany");

Example: Update a View

We use the SQL UPDATE command to update a view just like we update a table in a database. Use the below syntax to update a view. Note that if you update a view, it also updates base table data automatically.

    UPDATE view_name
    SET VALUE
    WHERE condition;

Example: Delete a Row from a View

The below SQL query deletes a row in the view Emp_View just like a table using the DELETE statement. Note that deleting a row from a view first delete the row from the actual table and the change is then reflected in the view.

    DELETE FROM Emp_View
    WHERE Emp_Name = "Samuel";

Example: Drop a View

The below SQL query drops a view using the DROP VIEW command.

    DROP VIEW view_name;

Output:

Types of Views

There are mainly two types of views: Simple Views and Complex Views. The below table shows the difference between the two views.

Simple View	Complex View
It is created from one table.	It is created from one or more tables.
It does not contain functions.	It contains functions.
It does not contain groups of data.	It contains groups of data.
DML through Simple View is allowed.	DML through Complex View is not allowed.

Conclusion

In this article, we discussed SQL Views using examples. We learned how to create a view, insert records in a view, delete a view, update a view and drop a view. All these operations are similar to table operations in SQL.

---

---