Simple CTE
Learn how to create and use a simple Common Table Expression in SQL Server.
What is a Simple CTE?
A simple CTE is a Common Table Expression that defines a temporary result set and uses that result in the query immediately following the CTE.
A simple CTE normally contains a single SELECT statement inside the CTE definition.
Basic Syntax
WITH CTE_Name AS
(
SELECT column1,
column2
FROM TableName
)
SELECT *
FROM CTE_Name;
Understanding the Syntax
| Part | Meaning |
|---|---|
| WITH | Starts the CTE definition. |
| CTE_Name | Name assigned to the CTE. |
| AS | Introduces the query that defines the CTE. |
| SELECT | Retrieves the required columns. |
| FROM | Specifies the source table. |
Example Table
Let's consider an Employees table:
EmployeeId | EmployeeName | Department | Salary
----------------------------------------------------
101 | Amit | IT | 60000
102 | Rahul | HR | 45000
103 | Priya | IT | 70000
104 | Sneha | Finance | 55000
Simple CTE Example
Suppose we want to retrieve all employees from the IT department.
WITH ITEmployees AS
(
SELECT EmployeeId,
EmployeeName,
Department,
Salary
FROM Employees
WHERE Department = 'IT'
)
SELECT *
FROM ITEmployees;
Result
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 101 | Amit | IT | 60000 |
| 103 | Priya | IT | 70000 |
CTE with WHERE Clause
A WHERE condition can be used inside the CTE to filter the data before the main query executes.
WITH HighSalaryEmployees AS
(
SELECT EmployeeId,
EmployeeName,
Salary
FROM Employees
WHERE Salary > 50000
)
SELECT *
FROM HighSalaryEmployees;
Selecting Specific Columns
We do not always need to select every column. We can select only the columns required by the application.
WITH EmployeeDetails AS
(
SELECT EmployeeName,
Department
FROM Employees
)
SELECT *
FROM EmployeeDetails;
Using CTE Result with ORDER BY
The main query can sort the data returned by the CTE.
WITH EmployeeDetails AS
(
SELECT EmployeeId,
EmployeeName,
Salary
FROM Employees
)
SELECT *
FROM EmployeeDetails
ORDER BY Salary DESC;
Simple CTE with Aggregate Function
A CTE can also contain aggregate functions such as COUNT, SUM and AVG.
WITH DepartmentSalary AS
(
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
)
SELECT *
FROM DepartmentSalary;
Advantages of Simple CTE
- Makes complex queries easier to read.
- Provides a meaningful name to an intermediate result.
- Helps organize query logic.
- Makes query maintenance easier.
- Can be combined with filtering, joins and aggregation.
Important Points
- A CTE starts with the WITH keyword.
- The CTE definition is enclosed inside parentheses.
- The query immediately following the CTE uses its result.
- A CTE is not a permanent database object.
- A simple CTE is useful for improving query readability.
Summary
A simple CTE is a temporary named result set created using the WITH keyword. It allows us to separate query logic and makes SQL statements easier to understand.
In the next topic, we will learn how to use a CTE with JOIN.