---
title: "How do I use CTE to simplify complex queries in SQL Server?"  
description: "Common Table Expressions (CTEs) are a powerful feature in SQL Server that can simplify complex queries by breaking them down into more manageable part"  
author: "Ravi Vishwakarma"  
published: 2024-07-03  
updated: 2024-07-04  
canonical: https://www.mindstick.com/blog/304466/how-do-i-use-cte-to-simplify-complex-queries-in-sql-server  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012"]  
reading_time: 4 minutes  

---

# How do I use CTE to simplify complex queries in SQL Server?

[**Common Table Expressions (CTEs)**](https://www.mindstick.com/forum/160190/how-is-cte-used-to-simplify-complex-sql-queries) are a powerful feature in SQL Server that can simplify complex queries by breaking [them down](https://answers.mindstick.com/qa/36760/do-pets-know-when-you-are-putting-them-down) into more manageable parts. A CTE is a temporary result set that you can reference within a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement.

Here's how to use CTEs to simplify complex queries:

## Basic Syntax of a CTE

The basic syntax of a CTE is as follows:

```plaintext
WITH CTEName (Column1, Column2, ...)
AS (
    -- CTE definition
    SELECT Column1, Column2, ...
    FROM YourTable
    WHERE Condition
)
-- Using the CTE in a query
SELECT *
FROM CTEName
```

Let's consider an example where you have the following tables:

## Employees Table:

| EmployeeID | FirstName | LastName | DepartmentID | Salary |
| --- | --- | --- | --- | --- |
| 1 | John | Smith | 1 | 50000 |
| 2 | Jane | Doe | 2 | 60000 |
| 3 | Michael | Johnson | 1 | 55000 |
| 4 | Emily | Brown | 3 | 52000 |

**[Departments](https://yourviews.mindstick.com/story/1782/important-ministries-and-departments-in-india) Table:**

| DepartmentID | DepartmentName |
| --- | --- |
| 1 | HR |
| 2 | Finance |
| 3 | IT |

## Using a CTE to Simplify a Query

Suppose you want to retrieve the names of employees along with their department names and [calculate the average](https://www.mindstick.com/forum/159301/write-a-mongodb-aggregation-query-to-group-and-calculate-the-average-of-a-field-in-a-collection) salary per department. You can use a CTE to simplify the query.

```plaintext
-- Define the CTE
WITH EmployeeDetails AS (
    SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName, e.Salary
    FROM Employees e
    JOIN Departments d ON e.DepartmentID = d.DepartmentID
),
AverageSalaries AS (
    SELECT DepartmentName, AVG(Salary) AS AvgSalary
    FROM EmployeeDetails
    GROUP BY DepartmentName
)
-- Using the CTEs in a final query
SELECT ed.FirstName, ed.LastName, ed.DepartmentName, ed.Salary, avs.AvgSalary
FROM EmployeeDetails ed
JOIN AverageSalaries avs ON ed.DepartmentName = avs.DepartmentName
ORDER BY ed.DepartmentName, ed.LastName;
```

#### Intermediate CTE Results:

## EmployeeDetails CTE:

| EmployeeID | FirstName | LastName | DepartmentName | Salary |
| --- | --- | --- | --- | --- |
| 1 | John | Smith | HR | 50000 |
| 2 | Jane | Doe | Finance | 60000 |
| 3 | Michael | Johnson | HR | 55000 |
| 4 | Emily | Brown | IT | 52000 |

## AverageSalaries CTE:

| DepartmentName | AvgSalary |
| --- | --- |
| HR | 52500 |
| Finance | 60000 |
| IT | 52000 |

### Final Result:

| FirstName | LastName | DepartmentName | Salary | AvgSalary |
| --- | --- | --- | --- | --- |
| John | Smith | HR | 50000 | 52500 |
| Michael | Johnson | HR | 55000 | 52500 |
| Jane | Doe | Finance | 60000 | 60000 |
| Emily | Brown | IT | 52000 | 52000 |

###

**[Explanation](https://yourviews.mindstick.com/view/84608/what-are-hindenburg-s-allegations-against-adani-detailed-explanation):**

**EmployeeDetails CTE**: This CTE retrieves the basic details of employees along with their department names by joining the `Employees` and `Departments` tables.

**AverageSalaries CTE**: This CTE calculates the [average salary](https://www.mindstick.com/forum/161556/how-do-you-select-employees-whose-salary-is-above-the-average-salary-of-their-department) for each department using the data from the `EmployeeDetails` CTE.

**Final Query**: The final query selects data from the `EmployeeDetails` CTE and joins it with the `AverageSalaries` CTE to include the average salary for each department in the result set.

## Benefits of Using CTEs:

1. **Readability**: Breaking down complex queries into smaller, more manageable CTEs makes the overall query easier to read and understand.
2. **Reusability**: You can define intermediate result sets once and reference them multiple times within the same query.
3. **[Maintenance](https://www.mindstick.com/articles/333912/maintenance-made-simple-how-online-tools-enhance-property-management)**: Queries are easier to maintain and modify because each part of the query is separated into logical sections.
4. **[Modularity](https://www.mindstick.com/interview/850/define-modularity)**: Complex logic can be encapsulated in CTEs, allowing you to build more modular and comprehensible queries.

By using CTEs, you can simplify and streamline complex queries, making them easier to write, read, and maintain in SQL Server.

[**Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL Server.**](https://www.mindstick.com/blog/304465/difference-between-inner-join-left-join-right-join-and-full-outer-join-in-sql-server)

[**Why do you use SQL Command and Queries in SQL Server?**](https://www.mindstick.com/interview/33942/why-do-you-use-sql-command-and-queries-in-sql-server)

---

Original Source: https://www.mindstick.com/blog/304466/how-do-i-use-cte-to-simplify-complex-queries-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.