What is View in SQL: SQL View is just like a regular table in SQL, but a table is an object which stores data in rows and columns, whereas View is a virtual table. It helps maintain data integrity, keeps the data secure and simplifies complex queries.

Instead of sending the complex query to the database all the time, you can save the query as a view and then

SQL View Syntax:

SELECT * FROM view

A view in SQL is a virtual table that does not store data itself. Instead, it shows data from one or more real tables by running a saved SQL query each time it is accessed. Views help simplify complex queries and let you see specific data without changing the original tables.

Top 10 SQL Query Optimization Techniques

What is View in SQL:

A view is a named SQL query stored in the database.
View can include joins, filtering conditions, grouping, and aggregation.
You can select data from a view just like a table using SELECT statement.
View do not store data, they display data from the underlying base tables.

How to Create a View in SQL:

The CREATE VIEW statement in SQL is used to create a virtual table based on the result of a SELECT query. A view does not store data physically but acts like a table that can be queried like any regular table.

Syntax to Create a View in SQL:

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

Create a Simple View:

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query:

CREATE TABLE Customer (
Id INT NOT NULL PRIMARY KEY,
Customer_Name VARCHAR (20) NOT NULL,
Age INT NOT NULL,
Address VARCHAR (50),
Salary DECIMAL (20, 2)
);

INSERT INTO Customer VALUES
(1, ‘Jack’, 20, ‘city1’, 4000.00 ),
(2, ‘Rose’, 19, ‘city2’, 3500.00 ),
(3, ‘Jim’, 21, ‘city3’, 4000.00 ),
(4, ‘Zin’, 24, ‘city4’, 6500.00 );

Now, we create a view that displays only the Id, Customer_Name, age and Salary of customers from the Customer table:

CREATE VIEW Customer_Salary_View
AS
SELECT Id, Customer_Name, Age, Salary
FROM Customer;

SELECT * FROM Customer_Salary_View;

SQL CASE Statement – A Comprehensive Guide

SQL View with WHERE Clause:

You can also create SQL views that include a WHERE clause to filter the data shown through the view. This allows you to present only a subset of rows from the base table(s), based on specific conditions.

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

CREATE VIEW High_Earning_Profiles AS
SELECT Id, Customer_Name, Age, Salary
FROM Customer
WHERE Salary > 4000;

CREATE statement helps us create a new View.
AS keyword tells us what the View is made up of.
SELECT statement selects different columns from different tables.
FROM table_name statement tells us the table from which we have selected the column.
WHERE condition is an optional statement that customises the data based on a condition.

Updating SQL View:

We can update the View by CREATE OR REPLACE VIEW statement, but we must consider some conditions before updating View.

Syntax
DROP VIEW IF EXISTS view_name;
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

DROP VIEW IF EXISTS statement drops the View if it already exists.
CREATE VIEW statement creates a new view with the updated query.
SELECT statement describes the new definition of the selected View.
FROM describes the tables from which View will take the data.
WHERE condition is a conditional statement that filters the data based on a condition.
HAVING condition is a conditional statement to filter the data based on aggregate functions.

We can use the CREATE OR REPLACE VIEW statement to add or replace fields from a view If we want to update the view MarksView and add the field AGE to this View from StudentMarks Table, we can do this by:

CREATE OR REPLACE VIEW Customer_Salary_View AS
SELECT Id, Customer_Name, Salary
FROM Customer;

Using Joins in Views:

You can create views using JOIN operations to combine and present data from multiple related tables as a single virtual table.

The view can include selected columns from each table and can even apply filters using a WHERE clause.

CREATE VIEW CUSTOMERS_ORDERS_VIEW AS
SELECT C.ID, C.NAME, O.ORDER_ID
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;

Dropping a View in SQL:

If a view is no longer needed, you can remove it using the DROP VIEW statement.

Syntax

Following is the basic syntax to drop a view in SQL:

DROP VIEW view_name;

Example

In the example below, we drop a view named CUSTOMERS_SALARY_VIEW:

DROP VIEW CUSTOMERS_SALARY_VIEW;

Listing all Views in a Database:

USE “DB_name”;
SHOW FULL TABLES WHERE table_type LIKE “%VIEW”;

Conclusion:

Views in Structured query language for the different database can help us to simplify complex queries into a simpler one. It also simplifies data access from multiple joined tables. It can be used as aggregated tables using group by operations. Views can be used for security purposes or can add extra value from the security point of view. It does not hold any space because it only has the definition in the data dictionary, not the copy of actual data.