Introduction

A common table expression (CTE) is a temporary named result table produced by a SELECT, INSERT, UPDATE, or DELETE statement that can be utilized in another statement. The CTE is a powerful SQL element that aids in query simplification. When a query is executed, the CTE acts as a virtual table (with records and columns) that is generated, used, and then deleted. This table frequently serves as a bridge to convert data from source tables to the format required by the query.

Syntax

A WITH clause that comes just before a SELECT, INSERT, UPDATE, DELETE or MERGE statement can be used to construct CTEs. A comma-separated list of CTEs can be included in the WITH clause. You can use the following syntax:

1WITH cte_name AS (
2  SELECT column_1, column_2, column_3
3  FROM table
4)
5SELECT column_1, column_3
6FROM cte_name
7WHERE ...

Where cte_name is the CTE’s name, while the CTE query is SELECT column_1, column_2, column_3 FROM table. The WITH keyword is used to begin the CTE. The name of your CTE is then specified, followed by the query's body in parentheses. The main query is the one after the closing parenthesis; SELECT column_1, column_3 FROM cte_name.