What is a CTE, and How Do You Write a CTE in SQL Server?

In this article, we are going to understand what CTE is, how we can write CTE in SQL server, and how it is useful.

What is CTE?

CTE stands for “Common Table Expressions”. It is used in SQL servers to define a temporary name result. CTE was introduced in SQL Server 2005; the common table expression (CTE) is a temporary named result set that you can reference within a Select, Insert, Update, or Delete statement. One more important thing is that CTE results aren’t stored anywhere but can always be referenced inside the query like any other table.

Why do we need CTE on the SQL server?

CTE is a powerful SQL feature that helps simplify queries. CTEs act as virtual tables (with records and columns) that are created during query execution, used by the query, and deleted after the query executes.

Syntax

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Let’s try to understand CTE Syntax.

Arguments

There are three arguments in the above syntax.

1. expression_name.
2. column_name.
3. query_derfination.

 

Let’s understand one by one.

1. expression_name: It should be a valid identifier for a common table expression. The expression_name must be different from the names of other common table expressions defined in the same WITH clause.
2. column_name: Specifies a column name in the common table expression. Duplicate names are not allowed within a single CTE definition. There are two rules for column names. The first is the number of column names specified, which must match the number of columns in the result set of the query_definition, and the second one is The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
3. query_defination: Specifies a SELECT statement whose result set satisfies a common table expression.

Some Examples

Before starting to explain some CTE examples, we need to create a database and some tables under the database, so run the following script in your SQL server.

USE [master]
GO

/****** Object:  Database [db_MyPractice]    Script Date: 06-10-2024 01:18:01 ******/
CREATE DATABASE [db_MyPractice]
    CONTAINMENT = NONE
    ON PRIMARY
    (
        NAME = N'db_MyPractice',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_MyPractice.mdf',
        SIZE = 8192KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 65536KB
    )
    LOG ON
    (
        NAME = N'db_MyPractice_log',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_MyPractice_log.ldf',
        SIZE = 8192KB,
        MAXSIZE = 2048GB,
        FILEGROWTH = 65536KB
    )
    WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

ALTER DATABASE [db_MyPractice] SET COMPATIBILITY_LEVEL = 150
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
    EXEC [db_MyPractice].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [db_MyPractice] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [db_MyPractice] SET ANSI_NULLS OFF
GO
ALTER DATABASE [db_MyPractice] SET ANSI_PADDING OFF
GO
ALTER DATABASE [db_MyPractice] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [db_MyPractice] SET ARITHABORT OFF
GO
ALTER DATABASE [db_MyPractice] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [db_MyPractice] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [db_MyPractice] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [db_MyPractice] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [db_MyPractice] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [db_MyPractice] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [db_MyPractice] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [db_MyPractice] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [db_MyPractice] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [db_MyPractice] SET DISABLE_BROKER
GO
ALTER DATABASE [db_MyPractice] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [db_MyPractice] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [db_MyPractice] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [db_MyPractice] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [db_MyPractice] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [db_MyPractice] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [db_MyPractice] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [db_MyPractice] SET RECOVERY SIMPLE
GO
ALTER DATABASE [db_MyPractice] SET MULTI_USER
GO
ALTER DATABASE [db_MyPractice] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [db_MyPractice] SET DB_CHAINING OFF
GO
ALTER DATABASE [db_MyPractice] SET FILESTREAM (NON_TRANSACTED_ACCESS = OFF)
GO
ALTER DATABASE [db_MyPractice] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [db_MyPractice] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [db_MyPractice] SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
ALTER DATABASE [db_MyPractice] SET QUERY_STORE = OFF
GO

USE [db_MyPractice]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees] (
    [EmployeeID] [smallint] NOT NULL,
      NOT NULL,
      NOT NULL,
      NOT NULL,
    [DeptID] [smallint] NOT NULL,
    [ManagerID] [smallint] NULL,
    CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED
    (
        [EmployeeID] ASC
    ) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON,
        OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tbl_book]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_book] (
    [BookId] [bigint] IDENTITY(1,1) NOT NULL,
      NOT NULL,
      NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Tbl_Bookissued]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Bookissued] (
    [transaction_id] [bigint] IDENTITY(1,1) NOT NULL,
    [StudentID] [bigint] NULL,
    [TeacherID] [bigint] NULL,
    [BookID] [bigint] NOT NULL,
    [issueDate] [date] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tbl_student]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_student] (
    [StudentID] [bigint] IDENTITY(1,1) NOT NULL,
      NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tbl_teacher]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_teacher] (
    [TeacherID] [bigint] IDENTITY(1,1) NOT NULL,
      NOT NULL
) ON [PRIMARY]
GO

-- Insert statements
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
(16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16),
(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
(275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)
GO

SET IDENTITY_INSERT [dbo].[tbl_book] ON
GO
INSERT [dbo].[tbl_book] ([BookId], [bookName], [className]) VALUES
(1, N'Maths', N'Class-1'),
(2, N'Maths', N'Class-2'),
(3, N'Maths', N'Class-3'),
(4, N'Maths', N'Class-4'),
(5, N'Maths', N'Class-5'),
(6, N'Maths', N'Class-6'),
(7, N'Hindi', N'Class-1'),
(8, N'Hindi', N'Class-2'),
(9, N'Hindi', N'Class-3'),
(10, N'Hindi', N'Class-4'),
(11, N'Hindi', N'Class-5'),
(12, N'Hindi', N'Class-6'),
(13, N'English', N'Class-1'),
(14, N'English', N'Class-2'),
(15, N'English', N'Class-3'),
(16, N'English', N'Class-4'),
(17, N'English', N'Class-5'),
(18, N'English', N'Class-6')
GO
SET IDENTITY_INSERT [dbo].[tbl_book] OFF

After running this script, you will have the following table.

Following tables with data.

• Student table: tbl_student
  
• Teacher Table: [tbl_teacher]
  
• Table book: tbl_book
  
• Table Book Issued: Tbl_Bookissued
  

A. Create a common table expression: The following example shows the total number of books issued per year for each student during their all-academic years.

-- Define the CTE expression name and column list.
WITH book_Issue_CTE (StudentID, StudentName, BookID, AcademicYear)
AS
(
    -- Define the CTE query.
    SELECT 
        BI.StudentID,
        StudentName,
        BookID,
        YEAR(issueDate) AS AcademicYear
    FROM 
        Tbl_Bookissued BI
    JOIN 
        tbl_student s ON BI.StudentID = s.StudentID
    WHERE 
        BI.StudentID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT 
    StudentID,
    StudentName,
    COUNT(BookID) AS TotalBookIssued,
    AcademicYear
FROM 
    book_Issue_CTE
GROUP BY 
    AcademicYear, StudentID, StudentName
ORDER BY 
    StudentID, AcademicYear;

B. Use a common table expression to limit counts and report averages: The following example shows the average number of books issued for all years for the students.

WITH Avg_book_Issue_CTE (StudentID, NumberOfBooks) AS
(
    SELECT StudentID, COUNT(*)
    FROM Tbl_Bookissued
    WHERE StudentID IS NOT NULL
    GROUP BY StudentID
)
SELECT AVG(NumberOfBooks) AS "Average book issued Per Student"
FROM Avg_book_Issue_CTE;

C. Use multiple CTE definitions in a single query: The following example shows how to define more than one CTE in a single query. Notice that a comma is used to separate the CTE query definitions.

In the example below, we are fetching records of books issued to teachers and students.

WITH book_issued_student_CTE (StudentID, BookIssued, IssueYear) AS
-- Define the first CTE query.
(
    SELECT StudentID, COUNT(BookID) AS BookIssued, YEAR(issueDate) AS IssueYear
    FROM Tbl_Bookissued
    WHERE StudentID IS NOT NULL
    GROUP BY StudentID, YEAR(issueDate)
),

-- Define the second CTE query, which returns sales quota data by year for each sales person.
book_issued_Teacher_CTE (TeacherID, BookIssued, IssueYear) AS
(
    SELECT TeacherID, COUNT(BookID) AS BookIssued, YEAR(issueDate) AS IssueYear
    FROM Tbl_Bookissued
    WHERE TeacherID IS NOT NULL
    GROUP BY TeacherID, YEAR(issueDate)
)

-- Define the outer query by referencing columns from both CTEs.
SELECT CTE.StudentID, 
       StudentName, 
       NULL AS TeacherID, 
       '' AS TeacherName, 
       BookIssued, 
       IssueYear
FROM book_issued_student_CTE CTE 
JOIN tbl_student s ON CTE.StudentID = s.StudentID

UNION ALL

SELECT '', 
       '', 
       CTE.TeacherID, 
       TeacherName, 
       BookIssued, 
       IssueYear
FROM book_issued_Teacher_CTE CTE 
JOIN tbl_teacher t ON CTE.TeacherID = t.TeacherID

ORDER BY CTE.StudentID, TeacherID;

Will get the following result.

D. Use a recursive common table expression to display multiple levels of recursion: The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.

Create one more table to proceed with this example.

-- Create an Employee table.
CREATE TABLE dbo.Employees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR(30) NOT NULL,
    LastName NVARCHAR(40) NOT NULL,
    Title NVARCHAR(50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID SMALLINT NULL,
    
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
    CONSTRAINT FK_Employees_ManagerID_EmployeeID 
        FOREIGN KEY (ManagerID) REFERENCES dbo.Employees (EmployeeID)
);
-- Populate the table with values.
INSERT INTO dbo.Employees 
VALUES
    (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
    (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
    (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
    (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
    (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
    (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
    (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
    (16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
    (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

After running this script we have the following data.

The following example shows the hierarchical list of managers and the employees who report to them.

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT 
        ManagerID, 
        EmployeeID, 
        Title, 
        0 AS EmployeeLevel
    FROM dbo.Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    SELECT 
        e.ManagerID, 
        e.EmployeeID, 
        e.Title, 
        EmployeeLevel + 1
    FROM dbo.Employees AS e
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)

SELECT 
    ManagerID, 
    EmployeeID, 
    Title, 
    EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;

The output of this CTE will be.

E. Use a recursive common table expression to display two levels of recursion: The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.

WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.Employees AS e
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2;

The result of this CTE will be.