Common Table Expression in SQL

This blog describes about the Common Table Expression in SQL (CTE IN SQL) with example. It also describes the difference between CTE, View and Temp table.

Getting Started

A CTE in SQL Server(CTE SQL), 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 expressions in this fashion.

Syntax:-

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

CTE with example in SQL Server

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

Difference between CTE in SQL Server and View in SQL Server

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 stored 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 a view or derived table cannot call itself, whereas CTE can call itself and hence support recursion.

Difference between CTE and Temp Table

CTE in SQL

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 in SQL

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.

Examples:-

The below example creates a Common Table Expression in SQL from a single table.

 WITH Student_CTE (StudentID,StudentName, ClassID)  
 AS  
 (SELECT StudentID,StudentName  
     ClassID  
  FROM  Student)  
 SELECT StudentID,StudentName, ClassID FROM  Student _CTE  

The below example creates a Common Table Expression in SQL from mutliple table using join

 WITH Student_CTE (StudentID,ClassName)  
 AS  
 (SELECT S.StudentID,  
      S.StudentName,  
     C.ClassName  
  FROM  Student S JOIN Class C ON S. StudentID=C.ClassID)  
 SELECT StudentID,ClassName FROM  Student _CTE  

CTE in SQL Server example

Example of Common Table Expression (recursive CTE in SQL Server)

 WITH ManagerReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS    
 (   
   SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel   
   FROM dbo.MyEmployees    
   WHERE ManagerID IS NULL   
   UNION ALL   
   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;    

CTE example in SQL Server

Summary

In the above of this blog, we discussed what is CTE in SQL server, CTE in SQL server example, and how it is the difference from View and Temp table. I hope you have enjoyed it a lot.

Thanks