How to use the CTE In SQL

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

Syntax:

[WITH  [, …]]  

::=

cte_name [(column_name [, …])]

AS (cte_query) 

Argument

1. Expression name: A valid identifier for a common table expression. The expression_name must be different from the names of other common table expressions defined in the same WITH  clause, but the expression_name can be the same as the name of the base table or view. All references to expression_name in the query use the common table expression instead of the base object.
2. Column name: Specifies a column name in a common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names must match the number of columns in the CTE_query_definition’s result set. A list of column names is optional only if all the resulting columns in the query definition have different names.
3. CTE_QueryDefinition: Specifies a SELECT statement whose result set satisfies a common table expression. The SELECT statement for CTE_query_defining must meet the same requirements as creating a view, except that a CTE cannot define another CTE. For more information, see the Remarks section and CREATE VIEW (Transact-SQL). If multiple CTE_query_settings are defined, the query definitions must be combined with one of the set operators UNION ALL, UNION, EXCEPT, or INTERSECT.
    

The Common Table Expressions (CTE) were introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table was just unsuitable. CTE was introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE view, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.