SQL Server Common Expressions (CTE)

Brief introduction

For select query statements, in general, in order to make T-SQL code more concise and sustainable, the introduction of additional result sets in one query is decomposed through views, not subqueries, but the view is in the database as a system object, That's a bit of a luxury for a result set that needs to be used only once in a stored procedure or user-defined function.

A common table expression (Common table expression) is an attribute introduced after the SQL SERVER 2005 version. A CTE can be thought of as a temporary result set that can be referenced more than once in the next Select,insert,update,delete,merge statement. Use common expressions to make statements clearer and more concise.

Common table expressions actually provide the same functionality and view, but it doesn't keep SQL statements in our database like a view. Although a CTE is not required, it can be used to improve the readability of SQL

Microsoft Official gives the advantage of using CET:

• Write a recursive query (like a tree query)
• Use a feature that uses a similar view, but do not want to save the definition of the query SQL statement in the database
• To reference a return data SQL statement multiple times, you need to define it only once.

definition of common table expression (CTE)

The definition of a common expression is simple and contains only three parts:

1. The name of the common table expression (after with)
2. The name of the column involved (optional)
3. A SELECT statement (immediately after as)

)

Common table (CTE) expressions can be categorized as recursive common table expressions and non-recursive common table expressions, depending on whether recursion is recursive.

non-recursive common table expressions (CTE)

A non-recursive common table expression (CTE) is a query result that returns only one result set for an external query call. Does not invoke its own CTE in the statement it defines

Non-recursive common table expressions (CTE) are used in a way that is consistent with views and subqueries

For example, a simple non-recursive common table expression:

   One of the benefits of a common table expression is that it can be referenced more than once in the next statement:

With Cte_nameas (  select * from Sys_log) SELECT * from Cte_name a inner joins Cte_name B on a.f_id=b.f_id

Because the CTE can only be used in the next statement, the CTE name is not valid if used multiple times

Therefore, when you need to refer to multiple CTE in the next statement, you can define multiple, separated by commas

With cte_name1as (  select * from Sys_log), cte_name2as (  select * from Sys_log) SELECT * FROM Cte_name1unionselect * f Rom cte_name2

Recursive common table expressions (CTE)

For recursive common expressions, the implementation principle is the same, and you need to define two parts in the statement:

• Basic statement
• Recursive statements

The two parts of SQL are returned through the Union ALL connection result set:

For example, I now have a table with a tree structure, faculties > Majors > Age > Class

How do I find his father based on the class ID?

You can also query its subordinate number recursively by its parent number,

This is the convenience of the CTE.

Summary

The CTE is a very elegant existence. The greatest benefit of the CTE is the increased readability of code, which is one of the essential qualities of good code. Recursive CTE makes it easier and more enjoyable to implement complex queries in an elegant and concise manner.

SQL Server Common Expressions (CTE)