CTE in SQL Server

Common Table Expressions

Common Table Expressions are also called CTEs. This feature was introduced with SQL Server 2005. The CTE is preferred to use as an alternative to a Subquery/View.

Sub-queries

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.

Example

 

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

Why to use a CTE

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

Syntax

With aliastablename (column1,colun2,….)

AS

(Query)

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

Example

 

With salaryCTE(EmployeeID)

AS

(Select employeeID from salary where salary >=1000)

, EmpDetailsCTE( Name, EmployeeID ,salary)

AS

(

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 emploueeID from the employee table for the employeeID's of salaryCTE.

Advantages

 

• CTE improves the 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.