SQL Common Table Expression


This article describes about SQL Common Table Expression with example. It also describes the difference between CTE, View and Temp table.

Getting Started

A Common Table Expression, also called as CTE in short form, defines a temporary result set which you can then use in SELECT, INSERT, UPDATE, DELETE or MERGE statement to manage complicated queries. CTE is defined within the statement using the WITH operator. You can define one or more common table expression in this fashion.

 WITH CTE_NAME (Column1,Column2….. ColumnN)   
           Column1,Column2….. ColumnN   
 SELECT Column1,Column2….. ColumnN FROM  CTE_NAME  

SQL JOINs can be used inside the CTE to get record from multiple tables and more a common table expression can include references to itself. This called a recursive common table expression. A CTE acts same as SQL view, temp table or table variable, but there is bit difference between these. Let’s see the difference between CTE, view, temp table and table variable before going ahead to example of CTE or more details about CTE.

Difference between CTE and View

Besides the syntax, declaration and other basic differences, the below are the main differences between the CTE and View.

A CTE is a temporary/logical View, it is not store physically. The result for which is only available to the very next query after the CTE is defined. Whereas a View is a physical object that is present in the database.

The biggest difference between a CTE and View, is that, View or derived table cannot call itself, whereas CTE can call itself and hence support recursion.

Difference between CTE and Temp Table

  1. CTE is unindexable but the existing indexes on referenced objects can use
  2. CTE doest not have constraints
  3. It is disposable VIEWs
  4. Exist only until the next query is run
  5. Can be recursive
  6. Do not have dedicated stats, it relys on stats on the underlying objects
Temp Table
  1. Temp table is physically exist in tempdb
  2. it can be indexed.
  3. Constraints can be created in it.
  4. Persist for the life of the current CONNECTION
  5. It does not support recursive but can be referenced by other queries or subprocedures
  6. It has dedicated stats

Guidelines for CTE

  1. The CTE name must be different from the name of any other common table expression defined in the same WITH
  2. The number of column names specified must match the number of columns in the result set of the CTE query.
  3. Duplicate column name within a single CTE definition are not allowed.
  4. A query referencing a CTE can be used to define a cursor.
  5. Tables on remote servers can be referenced in the CTE.
  6. An external table can be referenced from a CTE.
  7. Multiple CTE query definitions can be defined in a CTE.


The below example creates a CTE from a single table.
 WITH Student_CTE (StudentID,StudentName, ClassID)  
 (SELECT StudentID,StudentName  
  FROM  Student)  
 SELECT StudentID,StudentName, ClassID FROM  Student _CTE  

Example of CTE from mutliple table using join
 WITH Student_CTE (StudentID,ClassName)  
 (SELECT S.StudentID,  
  FROM  Student S JOIN Class C ON S. StudentID=C.ClassID)  
 SELECT StudentID,ClassName FROM  Student _CTE  

Example of recursive common table expression
 WITH ManagerReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS    
   SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel   
   FROM dbo.MyEmployees    
   WHERE ManagerID IS NULL   
   SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1   
   FROM dbo.MyEmployees AS e   
     INNER JOIN DirectReports AS d   
     ON e.ManagerID = d.EmployeeID    
 SELECT ManagerID, EmployeeID, Title, EmployeeLevel    
 FROM ManagerReports   
 ORDER BY ManagerID;    

Kailash Chandra Behera

No comments:

Post a Comment