Difference Between CTE and VIEW in SQL

SQL language provides a wide range of clauses, statements, functions, and JOINs to ease the complexity of the various models. Have you ever heard about the CTE and VIEW in SQL? The CTE is a Common Table Expression that is frequently utilized in complex problems to increase the readability of the overall model. On the other hand, the VIEW is a new query created from the database. The new query is a reference to the next query. In this article, we will see the basics of CTE and VIEW in SQL and the differences between them. Let’s start with the basic introductions and examples of these concepts.

CTE (Common Table Expression)

CTE is a temporary result set with a name. The result set can be utilized in other queries for reference. For example, we can use ‘xyz’ CTE in various statements like SELECT, DELETE, UPDATE, and INSERT. To define the CTEs in an SQL query we need WITH clause. WITH clause in SQL is used for initialization of temporary sub-queries. The syntax of CTEs is simple.

Syntax:

WITH cte_xyz (column_1, column_2, ...) AS (

    SELECT ...
    FROM ...
    WHERE ...
)

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

In this syntax, we have defined the cte_xyz first and then utilized that CTE in the main query.

Example of CTE Implementation In SQL

Here we are using a sample table to implement CTE in query. Let’s find out the total transactions for individual products/elements from the table using CTE.

Sample Table:

CREATE TABLE Sales_table (
    TransactionID_info INT,
    ProductID_info INT,
    Category_info VARCHAR(50),
    Amount_info DECIMAL(10, 2)
);

-- Inserting sample data
INSERT INTO Sales_table VALUES (1, 101, 'A', 1000.00);
INSERT INTO Sales_table VALUES (2, 102, 'B', 2000.00);
INSERT INTO Sales_table VALUES (3, 103, 'A', 5000.00);
INSERT INTO Sales_table VALUES (4, 104, 'A', 3000.00);
INSERT INTO Sales_table VALUES (5, 105, 'B', 2500.00);

Code language: SQL (Structured Query Language) (sql)

Query:

WITH CategoryTotal_cte AS (
    SELECT
        Category_info,
        SUM(Amount_info) AS TotalSales_info
    FROM
        Sales_table
    GROUP BY
        Category_info
)
SELECT
    Category_info,
    TotalSales_info
FROM
    CategoryTotal_cte;
Code language: SQL (Structured Query Language) (sql)

We have two products A and B. In this query, we are calculating the total expenses of both products. As you can see, the ‘CategoryTotal_cte’ is utilized in the main query.

Output:

VIEW (Virtual Table)

A View is a table that exists only on the fly (that’s why it is called virtual) and is based on the outcome of a SELECT query. Unlike base tables as being something that is physically stored, a view is an abstract object created with a “saved query” which can be referenced like another table in other queries. Views are considered helpful for pocketing up complex queries, providing an added layer of abstraction over the tables below and making it needlessly simple to write more quotidian queries with a less cumbersome interface. The simple syntax of the VIEW in SQL is given below.

Syntax:

CREATE VIEW view_xyz AS
SELECT column_1, column_2
FROM table_info
WHERE condition_1;
Code language: SQL (Structured Query Language) (sql)

Example of VIEW Implementation in SQL

Let’s try to implement VIEW on the sample database. This query will help to see the table of elements from the specific category.

Sample Table:

CREATE TABLE Orders_table (
    OrderID_info INT,
    CustomerID_info INT,
    ProductName_info VARCHAR(50),
    OrderDate_info DATE,
    OrderAmount_info DECIMAL(10, 2)
);

INSERT INTO Orders_table VALUES (1, 11, 'A', '2024-01-01', 1000.00);
INSERT INTO Orders_table VALUES (2, 12, 'B', '2024-01-02', 3000.00);
INSERT INTO Orders_table VALUES (3, 13, 'C', '2024-01-03', 50.00);
INSERT INTO Orders_table VALUES (4, 14, 'D', '2024-01-04', 500.00);
INSERT INTO Orders_table VALUES (5, 15, 'E', '2024-01-05', 800.00);
Code language: SQL (Structured Query Language) (sql)

Query:

CREATE VIEW HighValueOrders_info AS
SELECT 
    OrderID_info,
    CustomerID_info,
    ProductName_info,
    OrderDate_info,
    OrderAmount_info
FROM 
    Orders_table
WHERE 
    OrderAmount_info > 500.00;
    
SELECT * FROM HighValueOrders_info;
Code language: SQL (Structured Query Language) (sql)

This query will help to see the table of elements when we apply some conditions. We are using VIEW to get the products with a higher amount than 500.

Output:

Difference Between CTE and VIEW in SQL

CTE	VIEW
1. Defined as a WITH statement within a query.	1. Formulated through the CREATE VIEW command and stored in the database.
2. The Result Set is only restricted to the query in which it is defined and temporary.	2. It is stored on the database level and covers a wider range to be utilized in a variety of queries.
3. Is typically used to simplify complex queries that form a part of the main query	3. Once understood as a function to enhance and simplify complicated inference, it serves as a reusable abstraction layer above tables.
4. Live for the amount of time that a query is executed, does not remain stored in the database.	4. The persisted as a database object, it remains on the system until one drops it.
5. Allows recursive queries, so that the same query element can also refer to itself. Explicitly limited to the query in which it is defined: does not work for updates and inserts.	5. Whether a view is updatable depends not on the function of an individual view but on its complexity.

Summary

In this article, we have seen the basics of CTE and VIEW in SQL. The CTE and VIEW are really important to ease the complexity of the database. The concepts are explained in detail using the sample database. Example 1 and example 2 are based on the problems of CTE and VIEW respectively. After these examples, the difference between CTE and VIEW is also explained in detail. Hope you will enjoy this article.

Reference

https://stackoverflow.com/questions/30918633/sql-cte-vs-view