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

1. Select Name,Age, employeeID    
2. From employee    
3. Where employeeID in    
4. (   
5.    Select employeeID from salary where salary >=1000 /******Sub Query******/   
6. ) 

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

1. With aliastablename (column1,colun2,….)  
2.   
3. AS  
4.   
5. (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 

1. With salaryCTE(EmployeeID)  
2.   
3. AS  
4.   
5. (Select employeeID from salary where salary >=1000)  
6.   
7. , EmpDetailsCTE( Name, EmployeeID ,salary)  
8.   
9. AS  
10.   
11. (  
12.   
13. Select Name,Age, employeeID  
14.   
15. From employee Emp Join salaryCTE sa  
16.   
17. 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.