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.
WITH <common_table_expression_name> [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )
-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.
-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.
-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.
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.
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.