CTE in SQL Server


In this article, we will learn about Common Table Expressions in SQL Server with example queries and code examples.

Common Table Expression (CTE)

Common Table Expression or CTE in SQL is a temporary named result set created from a simple SELECT statement. The in-memory result set can be used within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. If we create the CTE in a Stored Procedure, I can’t use it in another Stored Procedure.

Sub-queries in SQL Server

A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.


Select Name,Age, employeeID    
From employee    
Where employeeID in    
   Select employeeID from salary where salary >=1000 /******Sub Query******/   

Why do we use a CTE in SQL Server?

In SQL, we will use sub-queries to join the records or filter the records from a sub-query. Whenever we refer to the same data or join the same set of records using a sub-query, code maintainability will be difficult. A CTE makes improved readability and maintenance easier.


With aliastablename (column1,colun2,….)    

We can use another CTE within a CTE but the query using the CTE must be the first query appearing after the CTE.


With salaryCTE(EmployeeID)    
(Select employeeID from salary where salary >=1000)    
, EmpDetailsCTE( Name, EmployeeID ,salary)    
  Select Name,Age, employeeID  
From employee Emp Join salaryCTE sa    
on Emp. employeeID = sa. EmployeeID)  

In the preceding example, first, we defined the CTE called salaryCTE. The SalaryCTE has a subquery that will select the employeeID from the salary table for the employee's salary greater than or equal to 1000. Then we have created one more CTE called EmpDetailsCTE that will select the name, age, and employeeID from the employee table for the employeeID's of salaryCTE.

Advantages of CTE

Here are some of the advantagss of using CTE. 

  • CTE improves code readability.
  • CTE provides recursive programming.
  • CTE makes code maintainability easier.
  • Though it provides similar functionality as a view, it will not store the definition in metadata.
  • Since data is in-memory, the query execution is much faster and hence increases performance. 

Difference between Temp Table and CTE

CTEs often compared to temp tables or derived tables. Here is a detailed comparision on the same:

Difference between CTE, Derived Table, Temp Table, Sub Query, and Temp Variable 


In this article, we learned about Common Table Expressions in SQL Server with example queries and code examples.

Still not sure? Want more CTE? Read here: What is CTE in SQL Server.


Similar Articles