Common Table Expression (CTE) In SQL Server

In this article, We will learn about Common Table Expression (CTE) in SQL Server.

CTE (Common Table Expression) is introduced in SQL server 2005. A CTE is hold a temporary result set,
that can be referenced with a SELECT, INSERT, UPDATE, or DELETE statement, which immediately follows the CTE.
CTE is defined by using WITH keyword.

Syntax,

WITH <common_table_expression_name> [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )

Here,

• common_table_expression_name:
  -It is an identifier of CTE.
  -expression_name must be different from the name of any other CTE defined in the same WITH <common_table_expression_name> clause.
  -But, expression_name can be the same as the name of a base table or view.
• column_name:
  -It specifies the column in CTE.
  -Duplicate column_names within a single CTE definition is not allowed.
  -The number of column names specified must match the number of columns in the result set of the CTE_query_definition.
  -The CTE column names and CTE query column names can be different.
  -The list of column names is optional only if unique names for all resulting columns are supplied in the query definition.
• CTE_query_definition:
  -Specifies a SELECT statement whose result set populates the common table expression.
  -If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

Example:

SQL Script to create Employee table:

CREATE TABLE Employee
(
Id int Primary Key,
Name nvarchar(50),
DeptId int
);

SQL Script to create Department table:

CREATE TABLE Department
(
DeptId int Primary Key,
DeptName nvarchar(50)
);

Insert data into Department table

Insert into Department values (1,'Dotnet');
Insert into Department values (2,'Angular');
Insert into Department values (3,' WordPress');
Insert into Department values (4,'ReactJS');

Insert data into Employee table

Insert into Employee values (1,'Mustakim', 3);
Insert into Employee values (2,'Priyank', 2);
Insert into Employee values (3,'Satish', 1);
Insert into Employee values (4,'Krutwik', 4);
Insert into Employee values (5,'Ruchi', 1);
Insert into Employee values (6,'Vibha', 3);

Write a query using CTE, to display the total number of Employees by Department Name. The output should be as shown below.

DeptName	TotalEmployees
Angular	1
ReactJS	1
Dotnet	2
WordPress	2

SQL query using CTE:

With CountEmployee(DeptId, TotalEmployees)
as
(
Select DeptId, COUNT(*) as TotalEmployees
from Employee
group by DeptId
)
Select DeptName, TotalEmployees
from Department
join CountEmployee
on Department.DeptId = CountEmployee.DeptId
order by TotalEmployees;

I hope you guys found something useful.

Thank You.