Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.
Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.
Let’s create a database with a student table and insert some dummy student records in it. We will use this database to write CTE queries. As always be sure you are well backed up before experimenting with a new code. See this article on SQL backup if you’re not sure.
Execute the following queries on your server.
CREATE DATABASE schooldb CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL, DOB datetime NOT NULL, total_score INT NOT NULL, ) INSERT INTO student VALUES (1, 'Jolly', 'Female', '12-JUN-1989', 500), (2, 'Jon', 'Male', '02-FEB-1974', 545), (3, 'Sara', 'Female', '07-MAR-1988', 600), (4, 'Laura', 'Female', '22-DEC-1981', 400), (5, 'Alan', 'Male', '29-JUL-1993', 500), (6, 'Kate', 'Female', '03-JAN-1985', 500), (7, 'Joseph', 'Male', '09-APR-1982', 643), (8, 'Mice', 'Male', '16-AUG-1974', 543), (9, 'Wise', 'Male', '11-NOV-1987', 499), (10, 'Elis', 'Female', '28-OCT-1990', 400);
Now, let’s create a very simple common table expression. This CTE will contain records of all students who were born before 1st January 1985. Take a look at the following script.
USE schooldb; WITH OldStudents AS ( SELECT * FROM student WHERE DOB < '1985-01-01' )
To create a CTE, you have to start with ‘WITH’ keyword followed by the name of the CTE and ‘AS’ keyword.
Next, within the parenthesis, you have to write the query which returns the records that the CTE will temporarily store. In the above script, we created a CTE named “OldStudents”.
However, note that if you try to execute the above query, you will get an error. This is because once you create a CTE, you have to immediately use it.
Let’s select all the records from our newly created “OldStudents” CTE. Try the following script on your server.
USE schooldb; WITH OldStudents AS ( SELECT * FROM student WHERE DOB < '1985-01-01' ) SELECT * FROM OldStudents
The above script will retrieve the following set of records:
Calculating Aggregate via CTE
Like tables, you can perform aggregate functions on CTE. Let’s take a look at another example of CTE.
USE schooldb; WITH SumofScores AS ( SELECT gender, SUM(total_score) as SumScore FROM student GROUP BY gender ) SELECT AVG (SumScore) FROM SumofScores
In the above example, we created a CTE named SumofScores. This CTE contains the sum of the values stored in the total_score column of the student table. The result is grouped by the gender column. The data stored by the CTE looks like this in the memory:
Next, we performed AVG function on the “SumScore” column of the CTE. The final result of the script will be the average of 2400 and 2730 i.e. 2565.
This is a bit more complicated than the previous example but demonstrates the concept of CTE more clearly.
Labeling Columns in CTE
In the previous example, we added an alias to the second column of the CTE. We renamed it as “SumScore”. This is one way of labeling columns in CTE and is similar to table column aliases.
However, there is another way of defining column names in CTE. Take a look at the following query.
USE schooldb; WITH SumofScores(Gender, SumScore) AS ( SELECT gender, SUM(total_score) FROM student GROUP BY gender ) SELECT AVG (SumScore) From SumofScores
In this script, we added the column names of the “SumofScores” CTE in the parenthesis after the CTE name. Each column name is separated by a comma.
If you look at the SELECT statement after the CTE, you can see that we are then referencing “SumScore” column that we created in the parenthesis after the CTE name.
Creating Multiple CTEs
All the examples so far have only used a single common table expression for clarity. You can create a list of CTEs at the same time and then use all of them in combination in the final result set.
This is best explained with the help of an example. Take a look at the following script below.
Here we will create two CTEs. The first CTE will store all the records of the students born before 1st January 1985. The second CTE will contain all the records of the students born on or after 1st January 1985.
After that, we will use select statements to retrieve all the records from both CTEs. The retrieved records will be merged together using UNION statement. Finally, the merged record will be sorted in the ascending order of date of birth.
USE schooldb; WITH OldStudents AS ( SELECT * FROM student WHERE DOB < '1985-01-01' ), YoungStudents AS ( SELECT * FROM student WHERE DOB >= '1985-01-01' ) (SELECT * FROM OldStudents UNION SELECT * FROM YoungStudents) ORDER BY DOB
In the above SQL query, we created two CTEs: “OldStudents” and “YoungStudents”. It is worth mentioning that you do not need to use the “WITH” keyword with every CTE. You are only required to use it before the first CTE in the script, after that you can create any number of CTEs by separating them with a comma.
The above script retrieves following results: