CTE in T-SQL: A Beginner’s Guide with 7 Examples

A common table expression (CTE) is a powerful T-SQL feature that simplifies query creation in SQL Server. CTEs work as virtual tables (with records and columns) that are created on the fly during the execution of a query. They are consumed by the query and destroyed after the query executes.

In some cases – like when the query expects data in a specific format and the source tables have the data in another format – a CTE can act as a bridge to transform the data in the source tables to the format expected by the query.

In this article, we will cover how you can create a CTE in T-SQL, the SQL dialect used by Microsoft SQL Server. To find out even more about CTEs in T-SQL, I recommend our interactive course Recursive Queries in MS SQL Server. It contains over 100 hands-on exercises on CTEs in T-SQL. You’ll practice everything covered in this article on your own!

Common Table Expressions (CTEs) in T-SQL

During the execution of a query, you can refer to the CTE as you would any regular table. It can be used in SELECT, INSERT, UPDATE, and DELETE statements. CTEs were not part of the original SQL language. They were introduced as a new feature in the SQL standard definition in 1999 (SQL 3). In 2005, they were made available in SQL Server.

The example SQL queries shown in this article will be based on the table olympic_games. The table contains the results of all the Olympic games, including 3 records (gold, silver, and bronze) for each sport.

Example 1: A Simple CTE

To explain CTE syntax in T-SQL, let’s write a simple query. Suppose we want a report with countries and the number of times each country won a gold metal in a marathon. We can create a CTE first called gold_in_marathon to return those rows of gold medals in men’s and women’s marathons. In the gold_in_marathon CTE, we only need the columns city, year and country.

Note that all CTEs start with the keyword WITH, followed by parentheses. In the parentheses, you write the query you want the CTE to return. Below is the complete query. With the CTE shown in bold type:

WITH gold_in_marathon AS
(
  SELECT 
    city, 
    year, 
    country
  FROM olympic_games
  WHERE medal_type = 'Gold' 
  AND sport IN ('Marathon Men', 'Marathon Women')
)
SELECT 
  country, 
  count(*) AS gold_medals_in_marathon 
FROM gold_in_marathon
GROUP BY country
ORDER BY gold_medals_in_marathon DESC;

In the above T-SQL query, you can identify two different queries. The first one is defined by the WITH clause enclosed in parentheses: this is the query that defines the content of the CTE. This is a regular SQL query: you can put in all the different SQL features (WHERE, GROUP BY, HAVING, UNION, etc.). The database uses the result to create a virtual table called gold_in_marathon. You can see the name of the CTE (gold_in_marathon) after the WITH clause.

The second query is the outer query. It references the CTE gold_in_marathon just like any other table. When the execution of the outer query is complete, the CTE gold_in_marathon is destroyed and you cannot reference it anymore.

Note that the CTE only has the columns city, year, and country from the table olympic_games. After creating the CTE, the database executes the outer query, which in turns reads the CTE gold_in_marathon, grouping the rows by country  and using the COUNT() function to obtain how many gold medals each country has.  Below are the results of the query:

Example 2: Using CTEs with Renamed (Aliased) Columns in T-SQL

In the next query, we will explicitly rename a column in the CTE by using an alias. Let’s suppose we want a report with a country ranking for marathon results (women and men). Each player will get 3 points for each gold medal, 2 points for each silver medal, and 1 point for each bronze medal. We will create a CTE called player_points to calculate each player’s points. As we will use a calculated column for the points, we will need to assign a name to this column using an alias. Let’s see the complete query:

WITH player_points AS
(
  SELECT 
    country,
    winner_name, 
    SUM(
      CASE medal_type 
        WHEN 'Gold' THEN 3
	  WHEN 'Silver' THEN 2
	  WHEN 'Bronze' THEN 1
	END
	)  AS player_total
    FROM   olympic_games
    WHERE sport in ('Marathon Men', 'Marathon Women')
    GROUP BY country,winner_name
)
SELECT 
  country, 
  SUM(player_total) AS country_points 
FROM player_points
GROUP BY country
ORDER BY country_points DESC;

In the player_points CTE, we calculate the points of each player using a calculated column, which doesn’t have a name. We need to define a name for this column in order to be able to refer to it in the outer query.

One way to define a name is by using an alias (later we will see another way) using the clause AS. You can see the column was named player_total in the CTE definition. In the outer query, we group the rows by country in order to calculate the total points of each country. Note we use the expression SUM(player_total) and again we use an alias to rename the column to country_points. The results of the query are below:

Before closing this section I would like to suggest the article What Is a CTE in SQL Server?, where you can find many examples of queries using CTEs in T-SQL. The article What Is a Common Table Expression (CTE) in SQL? covers the topic of CTEs in standard SQL; both articles are a good complement to this one. If you want a course about CTEs and recursive queries, I again suggest Recursive Queries in MS SQL Server.

Example 3: Using Multiple CTEs In The Same T-SQL Query

In this section, we will show two example queries that use more than one CTE. In the first example, we will use two independent CTEs and the main query will access both.

Let’s suppose we want a report with the names of the Olympic athletes who won at least one gold and one silver medal. The first CTE is called gold. After the CTE name, you can see the names of the columns (winner_name and gold_medals) explicitly defined within the parentheses. This is the other way to rename a column in the CTE.

The second CTE’s name is silver and it has two columns: winner_name and silver_medals. Note that we didn’t put the clause WITH before the second CTE. The WITH clause is used only one time before the first CTE definition. If we have more CTEs to define, we only need a comma before starting the definition of the next CTE(s).

WITH gold(winner_name,gold_medals) AS
(
  SELECT 
    winner_name, 
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Gold'
  GROUP BY winner_name
), 
silver(winner_name,silver_medals) AS
(
  SELECT 
    winner_name,
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Silver'
  GROUP BY winner_name
)
SELECT  
  g.winner_name, 
  g.gold_medals, 
  s.silver_medals
FROM gold g 
JOIN silver s 
ON g.winner_name = s.winner_name;

In the previous T-SQL query, we created two CTEs: gold and silver with the winner_name and the quantity of medals (gold or silver) won by each player. Then in the main query we joined both CTEs as if they were regular tables using a JOIN clause. As JOIN without keywords works as an INNER JOIN, only records for the same winner_name in both tables will be shown in the query result. The output is shown below:

Example 4: An SQL Server Query With a CTE Based On Another CTE

Next, we will create two CTEs in one query; however, the second CTE will be based on the first CTE. Suppose we want a query to obtain the TOP 3 countries for the quantity of medals won in one Olympic game. We don’t want to repeat countries, so if the first and second positions are for the same country, we want to show this country only one time. The query will be as follows:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS quantity_of_medals
  FROM olympic_games
  GROUP BY city, year, country
), 
country_best_game AS
(
  SELECT  
    country, 
    city, 
    year, 
    quantity_of_medals 
  FROM medals m1
  WHERE quantity_of_medals = ( 
    SELECT max(quantity_of_medals) 
    FROM medals m2
    WHERE  m1.country = m2.country
  )
)
SELECT TOP 3 country, 
  city, 
  year, 
  quantity_of_medals 
FROM country_best_game 
ORDER BY quantity_of_medals DESC;

In this query, we created one CTE called medals with the quantity of medals obtained by each country in each Olympic game where the country won at least one medal. After that, we will calculate a second CTE called country_best_game (based on the first CTE); it has one row for each country with the maximum number of medals obtained in a single game. Finally, in the main query, we select only the three countries with the most medals won in a single game. The results of the query are below:

At this point, I would like to suggest the article How to Learn SQL Common Table Expressions, where you can read about different approaches to learning CTEs. You’ll also see several example queries using CTEs in standard SQL.

Example 5: Using A CTE in a T-SQL INSERT

In T-SQL, CTEs can also be used in UPDATE, INSERT and DELETE statements. As a general rule, any SQL command allowing an embedded SELECT (for example a CREATE VIEW statement) can support a CTE. Let’s see an example of an INSERT using a common table expression in T-SQL.

Let’s suppose we have a table called country_medals_by_game with the columns country, city, game, number_of_medals, and delta_with_previous_game. The content of every column is clear, except for the column delta_with_previous_game. However, we will not use this column yet, so we will explain it later. The INSERT to populate the table is the following:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
INSERT INTO country_medals_by_game(city,year,country,quantity_of_medals)
SELECT city, year, country, medals_won
FROM medals;

In the previous INSERT we used a CTE called medals.  Note that the INSERT starts with the CTE definition (you can define multiple CTEs if you need). After the CTE is defined, the regular INSERT statement starts. In this example, the INSERT uses a SELECT statement, which in turn accesses the medals CTE defined previously.

Example 6: Using A CTE in a SQL Server UPDATE

The column delta_with_previous_game stores the difference of medals won by a country in two consecutive Olympic games. If the country increased the number of medals won, this column will have the difference in the number of medals as a positive value. If the country won fewer medals than in the previous Olympic game, the column will have a negative value. The UPDATE to populate the column is:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
UPDATE country_medals_by_game 
SET delta_with_previous_game = (
 SELECT COALESCE(quantity_of_medals - COALESCE(prevgame.medals_won,0),0)
 FROM medals prevgame
 WHERE country_medals_by_game.year = prevgame.year + 4
  AND country_medals_by_game.country = prevgame.country
);

In this UPDATE, we started with a WITH clause to define the same medals CTE we have been using. When the CTE definition section ends, the UPDATE statement starts. In the SET clause, we use a subquery to calculate the difference of medals won in two consecutive Olympic games. Note that the subquery accesses the CTE medals and the condition …

country_medals_by_game.year = prevgame.year + 4

 … is to match a row in country_medals_by_game with the row in medals for the previous Olympic game (occurring four years earlier). An interesting thing to note is this: For those countries that did not participate in two contiguous games, we set the column delta_with_previous_game to NULL. This indicates that we cannot calculate the difference; using a zero for this column is incorrect because it would mean that the country participated in the previous game, which it didn’t.

Example 7: Using CTEs for Recursive Queries in T-SQL

In SQL Server, it’s common to have tables representing hierarchies of data (like employee-manager, part-subpart, or parent-child). To traverse these hierarchies in any direction  – from top to bottom or from bottom to top – SQL Server uses a construct called recursive CTEs.

To have a data hierarchy in our Olympic database, we will add a pair of columns to the table olympic_games. Let’s suppose we want to identify those medals representing a world record. We can add a text column called record and set it with true when a medal is associated with a new world record.

Moreover, we know that every record breaks a previous record, so we will add another column called previous_record_medal where we will put the medal_id of the previous record. We now have a data hierarchy to read with a recursive query; let’s show a partial view of the olympic_games table with its new columns:

Let’s say we want to obtain a report with the list of world records in the men’s marathon. We can start by showing the current world record, and after that show the immediately previous record, and so on. We will need a recursive CTE in order to traverse the hierarchy of men’s marathon records. It will look like this:

WITH record_history (medal_id, year, winner, country, prev_record_medal_id) AS
(
  SELECT 
    medal_id, 
    year, 
    winner_name, 
    country, 
    previous_record_medal
  FROM olympic_games
  WHERE sport = 'Marathon Men' AND record = 'true'

  UNION ALL

  SELECT 
    og.medal_id,
    og.year,
    og.winner_name,
    og.country, 
    og.previous_record_medal
  FROM olympic_games og 
  JOIN record_history mrh 
  ON og.medal_id = mrh.prev_record_medal_id
)
SELECT * 
FROM record_history;

The CTE record_history is obtained as the result of a UNION ALL. The first query in the UNION is to obtain the current world record; note the condition record = true. After the UNION ALL, we have another query that obtains all the previous records in the men’s marathon. The key to connect a medal record row with the previous medal record row is the condition:

og.medal_id = mrh.prev_record_medal_id

The results of the query are below:

To prevent an infinite loop in a recursive CTE, there is a limit to the number of invocations allowed. In SQL Server, this is defined by default as 100. However, you can change this limit using the parameter MAXRECURSION at the end of the recursive query.

If you want to go deeper with recursive queries, I suggest the articles How to Write a Recursive CTE in SQL Server and Do it in SQL: Recursive SQL Tree Traversal. You’ll find several examples and different approaches to explain recursive queries.

CTEs Are a Powerful Feature of T-SQL Language

In this article, we covered how to use T-SQL CTEs to simplify complex queries for SQL Server databases. Before closing, I would like to suggest some articles related to SQL Server. The first one is Top 5 SQL CTE Interview Questions, where you can find tips on how to manage an interview for an SQL job. Another interesting article is How to Install Microsoft SQL Server 2019 and SQL Server Management Studio, where you can find help with installing SQL Server.

Finally I would like to encourage you to check out the course Recursive Queries in MS SQL Server. You can learn how to process trees and graphs in T-SQL and how to effectively organize your queries. Develop your skills and increase your assets!