CTE in SQL Server: Learn Common Table Expressions with Examples

CTE in SQL simplifies complex queries to make code more readable and provides a structured way to work with temporary result sets. 

In this article, we will explore what CTE is, its syntax, examples, why we need it, its types, advantages, and disadvantages of Common Table Expression (CTE), and how to use it in SQL Server.

CTE-IN-SQL

What is CTE in SQL Server?

A Common Table Expression (CTE) in SQL Server is a temporary result set that exists only within the scope of a single query and is automatically discarded once the query’s execution is completed.

A CTE can be reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex SQL queries more readable and manageable by breaking them into smaller, named, and reusable pieces. 

They are defined using the WITH keyword, followed by a CTE name and a query defining the CTE’s result set.

CTE Syntax in SQL Server

The basic syntax of a CTE looks like this:

WITH CTE_Name (Column1, Column2, ...) AS (
    -- CTE Query
    SELECT ...
)
SELECT * FROM CTE_Name;  
  • CTE_Name: This is the name of your CTE.
  • (Column1, Column2, ...): This is an optional list of column names to define the CTE’s structure.
  • AS: Indicates the start of the CTE query whose result set populates the CTE.
  • SELECT ...: Your SQL query that defines the CTE.
  • CTE_Name: At the last, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, or DELETE statement.

CTE Example:

Let’s create a simple table and then use a CTE to retrieve data from it. Suppose we have an Employees table with columns EmployeeID, FirstName, LastName, and ManagerID. We want to find the employees who report directly to a specific manager. Here’s how we can do it with a CTE:

-- Creating a table.
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    ManagerID INT
);

-- Inserting records into the table.
INSERT INTO Employees VALUES
(1, 'Shekh', 'Ali', NULL),
(2, 'Rohit', 'Sharma', 1),
(3, 'Mike', 'Johnson', 1),
(4, 'Emily', 'Davis', 2),
(5, 'Richard', 'Brown', 2);

Using a CTE to retrieve employees reporting to ManagerID is 1.

WITH DirectReports AS (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE ManagerID = 1
)
SELECT * FROM DirectReports;

Output:

EmployeeIDFirstNameLastName
2RohitSharma
3MikeJohnson

In this example, the CTE DirectReports filters the Employees table to retrieve those reporting to ManagerID is 1.

Why do We Need CTE in SQL Server?

CTEs are useful in SQL Server for several reasons:

  1. CTEs are valuable when we use the derived table (virtual table) multiple times within a single query. They allow us to define a temporary result set that can be referenced and reused throughout the query.
  2. CTEs are beneficial when we need to create an alternative to a view in the database. While views are predefined queries stored in the database, CTEs provide an ad-hoc way to structure and simplify complex queries within a specific query context.
  3. CTEs are helpful when we need to perform the same calculation multiple times on various query components simultaneously. They enable us to define the calculation logic once and apply it to different parts of a query, reducing redundancy and promoting consistency.
  4. CTEs become indispensable when we need to utilize ranking functions such as ROW_NUMBER(), RANK(), and NTILE(). These functions often require partitioning and ordering data within a query, and CTEs can help organize and manage these operations efficiently.

Types of CTE in SQL Server

CTEs in SQL Server can be categorized into two main types: Recursive and Non-Recursive CTEs.

Recursive CTE

Recursive CTEs are used when you need to work with hierarchical data, where a record refers to other records within the same table. These CTEs typically consist of two parts: the anchor member and the recursive member.

  • Anchor Member: The initial query that selects the root elements of the hierarchy. 
  • Recursive Member: The query that refers to the CTE itself and retrieves child elements.

To create a recursive CTE, you must use a UNION ALL statement that combines the anchor and recursive members.

The recursive member must reference the CTE itself within its query definition, creating a self-referencing loop. This self-reference is essential for the recursion to occur.

Here is an example of a recursive CTE to traverse an employee hierarchy:

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, FirstName, LastName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL -- Anchor member

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID -- Recursive member
)
SELECT * FROM EmployeeHierarchy;

Output:

Recursive-CTE-in-SQL-example

In this example, the anchor member selects employees without managers, and the recursive member retrieves employees reporting to each manager.

Non-Recursive CTE

Non-recursive CTEs are used for simpler queries where recursion is not required. They function like regular subqueries but offer the benefit of improved code readability.

Here is an example of a non-recursive CTE.

WITH EmployeeCount AS (
    SELECT COUNT(*) AS TotalEmployees
    FROM Employees
)
SELECT * FROM EmployeeCount;

Output:

5

In this example, we define a non-recursive CTE named EmployeeCount, which calculates the total number of employees by counting the rows in the Employees table.

The result is a single-row result set containing the total number of employees.

Advantages of CTE

Using CTEs in SQL Server brings several advantages to your SQL development:

  • Readability: CTEs enhance code readability by breaking down complex queries into smaller and more understandable parts, which makes your SQL code easier to read and troubleshoot. 
  • Code Reusability: CTEs allow you to define a query once and reference it multiple times in your main query, which reduces code duplication and improves maintainability.
  • Recursive Queries: CTEs are essential for handling recursive queries, where a query refers to its own output.
  • Performance: In some cases, CTEs can improve query performance by allowing the query optimizer to optimize execution plans better.

Disadvantages of CTE

While CTEs are helpful, they do have some limitations:

  • Performance: Overusing CTEs in a query can lead to performance issues. It’s essential to strike a balance between readability and performance.
  • Limited Scope: CTEs are temporary, and their scope is limited to the query in which they are defined and destroyed after query execution. Also, a CTE can be only specified in a SELECT statement.
  • Portability: CTEs are not supported in all database systems, so using them may limit your code’s portability.
  • Keyword Restrictions: CTE members cannot use certain clauses like DISTINCT, GROUP BY, HAVING, TOP, and JOINs directly within their definition. These clauses should be applied outside the CTE when needed.
  • Cannot be used in Stored Procedure Parameters: CTEs and table variables cannot be directly used as parameters in stored procedures. They are meant to be used within queries and cannot be passed as parameters like regular variables.
  • Lack of Nesting: Unlike views, CTEs cannot be nested. This means you cannot define one CTE within another, as you can with views, which can be nested to create more complex query structures.
  • Limited Reusability: CTEs are limited to the scope of the SQL query in which they are defined. They cannot be reused in other SQL queries, making views or temporary tables a better choice for broader reuse.
  • Column Count Must Match: The number of columns specified in the CTE definition (CTE arguments) must match the number of columns in the SELECT statement when referencing the CTE. Mismatched column counts will result in an error.

FAQs

Q1: What is a CTE in SQL?

A CTE (Common Table Expression) in SQL is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It’s defined using the WITH keyword and can break down complex queries into more manageable parts.

Q2: What are the types of CTEs in SQL?

There are two main types of CTEs in SQL: recursive CTEs and non-recursive CTEs.

Recursive CTEs are used for hierarchical data and require self-referencing queries.
Non-recursive CTEs are used for simpler queries without the need for recursion.

Q3: Are CTEs reusable across multiple queries?

No, CTEs are typically limited to the scope of the query in which they are defined. They cannot be reused in multiple queries. For broader reuse, views or temporary tables may be more appropriate.

Q4: Are there any limitations to using CTEs?

Yes, there are limitations. CTE members cannot directly use specific keyword clauses like DISTINCT, GROUP BY, HAVING, and JOINs within their definitions. Also, a CTE can only be referenced once in a recursive member and cannot be nested.

References: MSDN-CTE in SQL

You might want to read this too:

Shekh Ali
5 1 vote
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments