What is CTE in SQL

CTE represents a common table expression, which is a temporary named result set and always returns a result set. It was introduced by standard SQL to simplify SQL queries. The following article will introduce you to CTE (common table expression), I hope it will be helpful to you.

What is CTE?

A common table expression (CTE) can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and only persists during queries. Unlike derived tables, CTEs can be self-referential and referenced multiple times in the same query. [Related video tutorial recommendation: MySQL tutorial]

The structure of CTE

CTE is represented by the expression name of CTE , composed of AS keyword and SELECT statement. After a CTE is defined, it can be referenced like a table or view in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure of CTE is:

WITH Expression_Name [ ( ColumnName [1,...n] ) ]
AS
( CTE query definition )

Description: We can define CTE by adding WITH clause directly before SELECT, INSERT, UPDATE, DELETE or MERGE statement. The WITH clause can contain one or more comma-separated CTEs.

The declaration to run the CTE is:

SELECT <column_list>
FROM expression_name;

Types of CTE

There are two types of CTE: Recursive and non-recursive.

Recursive CTE: is a common table expression that refers to itself.

Non-recursive CTEs, as the name implies, do not use recursion; they do not reference themselves.

Benefits of using CTE

○ Readability: CTE improves readability. Rather than lumping all query logic into one large query, create several CTEs that will be combined later in the statement. This allows you to get the required chunks of data and combine them in the final SELECT.

○ Alternative View: You can replace the view with CTE. This is convenient if you don't have permission to create a view object, or if you don't want to create one because it will only be used in this one query.

○ Recursion: Using CTE will create a recursive query, that is, you can call your own query. This is convenient when you need to work with hierarchical data such as organizational charts.

○ Limitations: Overcome SELECT statement limitations, such as referencing itself (recursion) or using non-deterministic functions to perform GROUP BY.

○ Ranking: Whenever you want to use ranking functions, such as ROW_NUMBER(), RANK(), NTILE(), etc.

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of What is CTE in SQL. For more information, please follow other related articles on the PHP Chinese website!