Common Table Expressions (CTE)

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

Advantages of CTE

• 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.