What Is SQL Common Table Expression (CTE)


Common Table Expression (CTE) was introduced in SQL Server 2005, which offers a more readable form of the derived table. A Common Table Expression is an expression that returns a temporary result set. This result set is similar to a hybrid Derived Table. The resultset can be declared once and referenced multiple times in a query. It does not require any extra effort to declare it. 
CTE is more powerful than the derived table. It is capable of self-referencing and we can also use CTE multiple times in the same query. Mostly CTE is used to improved readability and makes it easy to maintain complex queries.
CTE can be used for both selects and DML (Insert, Update, and Delete) statements.

Common Structure of CTE 

  1. ;WITH CTE_name [ ( column_name [,...n] ) ]    
  2. AS    
  3. (    
  4. query_definition    
  5. )    
  6. select * from CTE_name;   
The Common Table Expression is created using the WITH statement followed by the CTE name and List of Columns (specifying a column is optional). After the "AS", the statement used to populate the returning columns begins. The CTE is then followed by a select calling it. Always begin CTE with a semi-colon.

Step 1. Create a query 

The following is a sample of creating two tables, EmployeeMasters and DepartmentMasters, and inserting some default values into them: 
  1. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentMasters]'AND type in (N'U'))    
  2. DROP TABLE [dbo].[DepartmentMasters]    
  3. CREATE TABLE [dbo].[DepartmentMasters](    
  4. [DepartmentId] [int] IDENTITY(1,1) NOT NULL,    
  5. [DepartmentCode] [varchar](50) NULL,    
  6. [DepartmentName] [varchar](50) NULL,    
  8. (    
  9. [DepartmentId] ASC    
  11. ON [PRIMARY]    
  12. GO    
  14. GO    
  15. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMasters]'AND type in (N'U'))    
  16. DROP TABLE [dbo].[EmployeeMasters]    
  17. CREATE TABLE [dbo].[EmployeeMasters](    
  18. [EmployeeId] [int] IDENTITY(1,1) NOT NULL,    
  19. [EmployeeName] [varchar](50) NULL,    
  20. [EmployeeCode] [varchar](50) NULL,    
  21. [DepartmentId] [intNULL,    
  23. [EmployeeId] ASC    
  25. ON [PRIMARY]    
  26. GO    
  28. GO    
  29. ALTER TABLE [dbo].[EmployeeMasters] WITH CHECK ADD CONSTRAINT [FK_EmployeeMaster_DepartmentMaster] FOREIGN KEY([DepartmentId])    
  30. REFERENCES [dbo].[DepartmentMasters] ([DepartmentId])    
  31. GO    
  32. ALTER TABLE [dbo].[EmployeeMasters] CHECK CONSTRAINT [FK_EmployeeMaster_DepartmentMaster]    
  33. GO    
  34. INSERT INTO DepartmentMasters VALUES    
  35. ('Eaxm''Examination'),    
  36. ('Staff''Exam Staff')    
  37. INSERT INTO EmployeeMasters VALUES    
  38. ('Jignesh','D0093',1),    
  39. ('tejas','D0094',1),    
  40. ('Rakesh','D0095',1),    
  41. ('Umesh','D0096',2),    
  42. ('Punit','D0097',2)   

Step 2. Writing CTE Query

The following is a sample use of a CTE Query: 
  1. ;WITH emp_detail(EmployeeName,EmployeeCode,DepartmentCode)    
  2. AS    
  3. (    
  4. SELECT e.EmployeeCode,e.EmployeeName,d.DepartmentCode FROM EmployeeMasters e JOIN DepartmentMasters d ON e.DepartmentId=d.DepartmentId    
  5. )    
  6. SELECT * FROM emp_detail;   

When to Use CTE

CTE offers the same functionality as a View (ideal for one-off usages). CTE offers the following four advantages: 
  • Create a recursive query. 
  • Alternative from a view when the general use of a view is not required, case in which, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement. 

Cannot use with CTE

  • The clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, FOR BROWSE, cannot be used in the CTE query definition.
  •  "SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries are not allowed in the CTE query definition of a recursive member.
  •  A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
  • Specifying more than one "WITH" clause in a CTE is not allowed. For example, if a CTE query definition contains a subquery then that subquery cannot contain a nested WITH clause to define other CTE. 


CTE provides a more readable and usable approach to derived tables. CTE is not materialized into a work table (temporary table). CTEs are not a replacement of Temporary Tables. The scope of the CTE is limited to the first SELECT statement only.