How to Write SQL Server Queries Correctly Using Common Table Expressions (CTEs)

SQL Server is one of the most powerful relational database management systems, and mastering its querying capabilities is essential for efficiently retrieving and manipulating data. One of the most useful features for writing clean and readable queries is Common Table Expressions (CTEs). In this blog, we’ll explain how to use CTEs correctly in SQL Server, explore their benefits, and provide practical examples.

What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It allows you to break complex queries into simpler, more understandable parts. CTEs can be thought of as “named result sets” that can be referenced multiple times within a query, making it easier to write and maintain SQL code.

CTEs improve the readability of queries, especially when dealing with complex joins or recursive data.

Syntax of a Common Table Expression

The basic syntax of a CTE is as follows:

WITH CTE_Name AS (

    — Query that defines the CTE

    SELECT column1, column2

    FROM table_name

    WHERE condition

)

— Main query that uses the CTE

SELECT *

FROM CTE_Name;

• WITH: This keyword starts the CTE definition.

• CTE_Name: The name of the CTE.

• SELECT: The query that defines the CTE’s result set.

• Main query: After the CTE is defined, you can reference it in the main query as if it were a regular table or view.

Benefits of Using CTEs

1. Improved Readability: CTEs allow you to break down complex queries into smaller, more readable parts.

2. Reusability: You can reference a CTE multiple times in the same query, reducing the need to repeat code.

3. Recursive Queries: CTEs can be used for recursive queries, where the output of one part of the query is fed into the next.

4. Better Organization: CTEs help organize your SQL logic into manageable sections, making maintenance easier.

5. Avoiding Subqueries: CTEs often eliminate the need for nested subqueries, simplifying your query structure.

Examples of Using CTEs

1. Basic CTE Example

Let’s begin with a simple example of a CTE used to filter and join data from two tables:

WITH EmployeeCTE AS (

    SELECT EmployeeID, FirstName, LastName, DepartmentID

    FROM Employees

    WHERE DepartmentID = 2

)

SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName

FROM EmployeeCTE e

JOIN Departments d ON e.DepartmentID = d.DepartmentID;

• Explanation:

• The CTE named EmployeeCTE selects employees from the Employees table who belong to a specific department (DepartmentID = 2).

• The main query then uses this CTE to join it with the Departments table and display employee names and their respective department name.

2. CTE with Aggregation

CTEs can also be useful when performing aggregations. Let’s say you want to calculate the average salary per department, and you also want to return departments where the average salary exceeds a certain threshold.

WITH DepartmentAvgSalary AS (

    SELECT DepartmentID, AVG(Salary) AS AvgSalary

    FROM Employees

    GROUP BY DepartmentID

)

SELECT d.DepartmentName, das.AvgSalary

FROM DepartmentAvgSalary das

JOIN Departments d ON das.DepartmentID = d.DepartmentID

WHERE das.AvgSalary > 50000;

• Explanation:

• The DepartmentAvgSalary CTE calculates the average salary per department.

• The main query joins this CTE with the Departments table and filters to return only those departments where the average salary exceeds 50,000.

3. Recursive CTE

Recursive CTEs are used when you need to query hierarchical or tree-like data structures, such as organizational charts or folder structures. Below is an example of a recursive CTE to display employee hierarchies:

WITH EmployeeHierarchy AS (

    — Anchor member: Select top-level employees (without manager)

    SELECT EmployeeID, FirstName, LastName, ManagerID

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    — Recursive member: Select employees managed by someone in the hierarchy

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID

    FROM Employees e

    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID

)

SELECT EmployeeID, FirstName, LastName, ManagerID

FROM EmployeeHierarchy;

• Explanation:

• The CTE starts by selecting employees who do not have a manager (ManagerID IS NULL).

• The recursive part of the CTE uses UNION ALL to join employees with their managers, effectively building the hierarchy level by level.

• The result is a list of employees along with their managers.

When to Use CTEs

While CTEs offer many advantages, it’s important to know when and why to use them:

• Complex Queries: When dealing with queries that involve multiple joins, filtering, or subqueries, CTEs make your SQL easier to read and maintain.

• Recursive Data: When working with hierarchical data, such as organizational structures, CTEs provide a simple way to query data recursively.

• Refactoring Subqueries: If you have subqueries within your main query, refactoring them into a CTE can make the query much cleaner.

However, avoid using CTEs for simple queries where a regular JOIN or WHERE clause would suffice, as they can add unnecessary complexity.

Conclusion

Common Table Expressions (CTEs) are an incredibly useful tool for writing clear, efficient, and maintainable SQL Server queries. They provide a way to organize complex queries, reuse result sets, and even handle recursive data. Whether you’re aggregating data, creating hierarchies, or simplifying your queries, CTEs can significantly enhance the readability and performance of your SQL code.

By mastering CTEs, you can improve both the efficiency and clarity of your SQL Server queries, helping you write cleaner and more optimized code in your daily database management tasks.

Feel free to experiment with CTEs in your own SQL queries and take advantage of their ability to break down complex logic into manageable parts!