Common Table Expression (CTE) in SQL Server

What is CTE?

CTE stands for Common Table Expression. It allows us to define a temporary result set that we can query. It is not stored as an object in the memory and it only lasts for the duration of the query.

How do we create a CTE?

The basic syntax of CTE is as follows:

WITH cte_name (column_name1, column_name2...)
AS
( Write SQL statement here)

SELECT col_name
FROM cte_name;

WITH cte_name
AS
( Write SQL statement here)

SELECT col_name
FROM cte_name;

As shown in snippet 1 and 2, we start with a 'WITH' keyword followed by the name we want to give to our CTE. After that, we specify the aliases for the columns (as shown in snippet 1 as column_name1, column_name2...) but that is optional(See snippet 2. It is similar to snippet 1 except it does not have aliases) Next, we specify 'AS' and then define our SQL statement within the brackets. IMMEDIATELY after we define the CTE, we refer to the CTE and by referring we mean that we query the CTE as if we were to query any other table.

Let us see CTE in action with the help of an example. As shown below, we have a table called 'information' in which we have data about employees, their name, department and salary.

Now, let us create a CTE to find out the department(s) with 3 or more employees. First, we create a CTE that gives details about all the departments and the total number of employees in each department. Second, we query that CTE to fetch only those departments which have 3 or more employees.

We can see in the above-mentioned image that there are 4 departments with 3 or more employees.

Also, we need to keep in mind that since CTE temporarily holds the data, we need to query it IMMEDIATELY after we define it. If we specify any other query between our CTE and the SQL statement that queries that CTE, then we will get an error as shown in the picture below.

How is CTE useful?

1) It makes code simpler, reduces the complexity and hence, improves readability.

2) It can be used as a substitute for a view. (In case we do not have permission to create a view or we just want the query to run for one time and do not require to store it as a view)

3) It comes to the rescue when we want to perform an aggregate function on an aggregate function (multi-level aggregations). Let us see an example of this. We want to find out the average maximum salary. So, we will try to use AVG and MAX function on the salary column.

As we can see in the picture above, we get an error stating that we cannot perform an aggregate function on an aggregate. In such cases, using CTE would resolve the issue. Let us see how it can be done.

As we can see in the picture above, we can first create a CTE which temporarily holds the data of each department and its maximum salary and then we can query that to get the average of maximum salaries.