Views in SQL

Views in SQL are like virtual tables that do not store data themselves but act as saved SQL queries that we can reuse. We can think of views as a snapshot of a query result. Views give us a simplified way to look at or interact with the data.

Views are quite handy in improving query readability. They can also be used for restricting access to sensitive data, or combining data from multiple tables into a single view. In this chapter, we will use a set of examples to demonstrate what views are, their types, and how to use them effectively in practical aspects.

What are SQL Views?

A view is a predefined SQL query stored in the database. It acts like a virtual table that updates automatically whenever the underlying data changes. We can use views to −

• Simplify complex queries
• Create customized perspectives for different users
• Restrict access to specific columns or rows

Sample Tables and Data

We will use the following set of tables in the examples of this chapter to demonstrate the practical aspects of using views in SQL −

Here is the Customers table −

customer_id	first_name	last_name	email	phone
1	Antara	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

This is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

And, the Orders table is as follows −

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00
4	1	103	1	2023-11-04 14:00:00

Creating Views in SQL

To work with views we must see how we can make a view from the existing table. We can create a view using the CREATE VIEW statement. When a view is created, we can query it like a table.

Example: View for Customer Orders

Let us create a view to display customer names along with the products they ordered.

CREATEVIEW CustomerOrders ASSELECT c.first_name, c.last_name, p.product_name, o.quantity, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

Use the following query to fetch all the records from the CustomerOrders view −

SELECT*FROM CustomerOrders;

It will fetch all the records from the view −

first_name	last_name	product_name	quantity	order_date
Antara	Joshi	Laptop	1	2023-11-01 10:00:00
Bimal	Saha	Office Chair	2	2023-11-02 12:30:00
Chandan	Tudu	Smartphone	1	2023-11-03 15:45:00
Antara	Joshi	Office Chair	1	2023-11-04 14:00:00

This view combines data from three tables, which makes it easier to fetch customer orders.

Updating the Data Using Views

Some views allow updates, but it depends on the complexity of the view and database constraints.

Example: Update through a Simple View

Let us make a view to display product stock levels.

CREATEVIEW ProductStock ASSELECT product_id, product_name, stock_quantity
FROM Products;

Use the following query to fetch the details from the ProductStock view −

SELECT*FROM ProductStock;

It will fetch the records from the ProductStock view −

product_id	product_name	stock_quantity
101	Laptop	10
102	Smartphone	20
103	Office Chair	5

Updating the Stock Quantity through the View

Let us now use the following query to update the ProductStock view −

UPDATE ProductStock
SET stock_quantity = stock_quantity -1WHERE product_id =101;

Now, query the ProductStock view again −

SELECT*FROM ProductStock;

It will fetch the updated data from the ProductStock view −

product_id	product_name	stock_quantity
101	Laptop	9
102	Smartphone	20
103	Office Chair	5

Here, we can see the update reflects in the underlying Products table as well.

Types of Views in SQL

SQL views can be categorized into two main types −

• Simple Views
• Complex Views

Let’s start with a simple example of Simple Views.

Simple Views

Simple views are based on a single table. Simple views allow data updates if no calculations or aggregate functions are used.

Example: Simple View for Customers

In this query, we are creating a simple view from the Customers table −

CREATEVIEW CustomerDetails ASSELECT first_name, last_name, email
FROM Customers;

Complex Views

Complex Views combine data from multiple tables or include calculations. They can also be aggregate functions or GROUP BY clauses. Complex Views generally do not allow updates.

Example: Complex View for Total Revenue

Let us make a view to calculate the total revenue for each product −

CREATEVIEW ProductRevenue ASSELECT p.product_name,SUM(o.quantity * p.price)AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUPBY p.product_name;

Use the following query to fetch the data from the ProductRevenue view −

SELECT*FROM ProductRevenue;

This complex view fetches the revenue generated by each product −

product_name	total_revenue
Laptop	1000.00
Smartphone	800.00
Office Chair	450.00

Dropping the Views

Like we delete tables, we can delete views as well. If we no longer need a view, we can delete it using the DROP VIEW statement.

Example: Drop a View

Use the following query to drop the ProductStock view −

DROPVIEW ProductStock;

It removes the ProductStock view but does not affect the underlying Products table.

Advantages and Disadvantages of Using Views

The following table highlights the advantages and disadvantages of using views in SQL queries −

Advantages	Disadvantages
Simplifies Complex Queries − We can save a complicated query as a view and reuse it easily.Restricts Data Access − Views can limit the columns or rows that certain users can see.Encourages Reusability − Once a view is created, it can be used in multiple queries.Improves Readability − Queries referencing views are generally easier to read and understand to work with.	Performance Overhead − Complex views can slow down performance, because the query runs every time we access the view.Limited Updates − Not all views allow data updates.Dependency Issues − Dropping or modifying the underlying tables can break views.