Common table expression

Common table expressions (CTE) are used to simplify queries "by deconstructing ordinarily complex queries into simple blocks to be used, and reused if necessary, in rewriting the query" (Chartio).

A CTE "can be thought of as a temporary result set that's 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's not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

"Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated" (Microsoft)).

"We can define CTEs by adding a WITH clause directly before SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs separated by commas. ...

"After you define your WITH clause with the CTEs, you can then reference the CTEs as you would refer to any other table. However, you can refer to a CTE only within the execution scope of the statement that immediately follows the WITH clause. After you’ve run your statement, the CTE result set is not available to other statements" (GeeksforGeeks).