Difference Between Simple and Complex View in SQL

Imagine that you have a bunch of files which contain information about the customers, their orders and the products they have bought. Views help us to see that data in a way that’s easier to understand and use. In this tutorial, we will learn about simple and complex views with the help of practical examples.

Database View: An Overview

Suppose you are the owner of a bookstore and have a huge stack of bills. And every time you have to find any particular bill you have to keep going through the entire stack. You would want to organize the bills to cut out the time taking steps. Who is going to repeatedly do it, after all?

That’s where views come into the picture. Views are like creating special folders for different types of information. You can put similar bills together in these folders so that when you need something specific, you can just open the right folder instead of searching through the whole pile.

Simple View

A simple view in SQL retrieves data from only one table, or it contains only one base table. Simple views are like creating a new folder where you put bills together based on some simple rule, such as in case you want to know who bought what from your store.

Example Table:

Suppose you run an online shopping website, and you have a record of Customers, the Products they bought and their Orders details. Below is the table that contains all these records which we are going to use for the rest of our tutorial:

Example 1:

As you run an online shopping website and there must have been scenarios where you wanted to track customer orders. The below query will create a simple view called CustomerOrders, which gives you a quick way to see who bought what.

CREATE VIEW CustomerOrders AS
SELECT Customers.Name, Orders.OrderID, Products.ProductName
FROM Customers
JOIN Orders ON Customers.CustomerID= Orders.CustomerID
JOIN Products ON Orders.ProductID = Products.ProductID;Code language: SQL (Structured Query Language) (sql)

You can then query the simple view just like you would a regular table:

SELECT * FROM CustomerOrders;Code language: SQL (Structured Query Language) (sql)

Output:

Example 2:

Now to track the stock of your products, you must know how many of them are sold, let’s create a simple view that shows the order placed for each product.

CREATE VIEW ProductOrders AS
SELECT Products.ProductID, Products.ProductName, COUNT(Orders.OrderID) AS TotalOrders
FROM Products
LEFT JOIN Orders ON Products.ProductID = Orders.ProductID
GROUP BY Products.ProductID, Products.ProductName;Code language: SQL (Structured Query Language) (sql)

This simple view aggregates the total number of orders for each product.

SELECT * FROM ProductOrders;Code language: SQL (Structured Query Language) (sql)

Output:

Also Read: MySQL Rename View

Complex View

Complex views go a step further. It enables more intricate query definitions. A Complex view in SQL retrieves data from more than one table. They are like creating a custom report that summarizes a lot of information.

Example 1:

Let’s say you want to see how well each product category sells in different regions, you can make a view called ‘Summary’.

CREATE VIEW Summary AS
SELECT Products.ProductCategory, 
       COUNT(Orders.OrderID) AS TotalOrders, 
       SUM(Orders.Revenue) AS TotalRevenue
FROM Products
LEFT JOIN Orders ON Products.ProductID = Orders.ProductID
GROUP BY Products.ProductCategory;Code language: SQL (Structured Query Language) (sql)

This view calculates the total orders and revenue for each product category based on the revenue stored in the Orders table.

SELECT * FROM Summary;Code language: SQL (Structured Query Language) (sql)

Output:

Example 2:

As you run an online website, you must track how much revenue are you generating with each product. To figure out that, let’s create a complex view that provides a summary of revenue which is generated by each product.

CREATE VIEW ProductRevenue AS
SELECT Products.ProductName, 
       SUM(Orders.Revenue) AS TotalRevenue
FROM Products
LEFT JOIN Orders ON Products.ProductID = Orders.ProductID
GROUP BY Products.ProductName;Code language: SQL (Structured Query Language) (sql)

This complex view will calculate the total revenue which is generated by each product.

SELECT * FROM ProductRevenue;Code language: SQL (Structured Query Language) (sql)

Output:

Difference Between Simple and Complex View

S No.	Simple View	Complex View
1	It is created from only one table.	It is created from more than one table.
2	It excludes base table columns that are NOT NULL.	NOT NULL Columns that are not selected in the simple view can be present in the complex view.
3	There are no group by, distinct, pseudocolumns like rownum, columns specified by expressions in a simple view.	Group by, distinct, pseudocolumns like rownum, and columns specified by expressions can all be found in complex view.
4	A simple view might be used to carry out DML operations.	Complex views could not always be used to carry out DML operations.
5	Group function can not used.	Group functions can be used.

Conclusion

Simple views are like looking at a single picture, where you can easily see what’s there without much extra stuff. Complex views are more like looking at a detailed map with lots of information layered on top, making it harder to see everything at once, but it offers more insights if you look closely. Both Simple view and complex view have their uses. If you want to analyze your data properly, you must include these in your project. We hope you enjoyed it.

Reference

https://stackoverflow.com/questions/31110965/simple-view-and-complex-view