Masting Common Table Expressions in SQL Server

As per my experience ,we describes the SQL Common Table Expressions (CTEs). It includes their syntax, benefits, and various applications. It presents a variety of types of CTEs, and illustrates with practical examples their strength. A CTE is a temporary table defined in an SQL statement. CTEs can be presented in the FROM clause and provide the query with an easy name. That name can be used again in the query.

What are CTEs?

A Common Table Expression ( CTE ) is a named result set that functions as a temporary named result set that you may refer to within a single query.

SQL-statement, such as SELECT, INSERT, UPDATE, or DELETE statement. CTEs are not storage

They are all objects; they exist in the course of the query running. The WITH clause defines them.

and they are capable of simplifying complex queries and simplifying the maintenance of queries.

Syntax of a CTE

The basic syntax of a CTE is as follows:

WITH CTE_Name AS (  
- CTE Definition  
(SELECT statement) )  
- Main Query (using the CTE)  

WITH CTE_Name AS: This introduces the CTE and assigns it a name (CTE_Name).
(-- CTE Definition): This is the SELECT statement that defines the result set of the CTE.
-- Main Query: This is the main query that uses the CTE as if it were a regular table or view.

What is the advantage of CTEs?

CTEs ( Common Table Expressions ) shorten and simplify the long queries.
A CTE may be used multiple times and thus reduces copy-and-paste programming.
The CTEs can be used in recursive queries, which is a handy option to deal with hierarchical data.
CTEs also serve as a place to store intermediate results, and that makes the main logic easier.

Examples

Here are the SQL Server queries to create the Employees and Departments tables and insert the sample data you provided:

-- Create Departments table  
CREATE TABLE Departments (  
    DepartmentID INT PRIMARY KEY,  
    DepartmentName VARCHAR(50),  
    Location VARCHAR(50)  
);  
  
-- Create Employees table  
CREATE TABLE Employees (  
    EmployeeID INT PRIMARY KEY,  
    FirstName VARCHAR(50),  
    LastName VARCHAR(50),  
    DepartmentID INT,  
    Salary INT,  
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)  
);   

-- Insert into Departments  
INSERT INTO Departments (DepartmentID, DepartmentName, Location) VALUES  
(10, 'Sales', 'New York'),  
(20, 'Marketing', 'London'),  
(30, 'Engineering', 'San Francisco');  
  
-- Insert into Employees  
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary) VALUES  
(1, 'John', 'Doe', 10, 60000),  
(2, 'Jane', 'Smith', 20, 75000),  
(3, 'Michael', 'Johnson', 10, 55000),  
(4, 'Emily', 'Brown', 30, 80000),  
(5, 'David', 'Wilson', 20, 70000),  
(6, 'Sarah', 'Garcia', 10, 62000),  
(7, 'Robert', 'Martinez', 30, 85000);  

------Departments Rable OutPut  
DepartmentID DepartmentName                                     Location  
------------ -------------------------------------------------- --------------------------------------------------  
10           Sales                                              New York  
20           Marketing                                          London  
30           Engineering                                        San Francisco  

------Employees Rable OutPut  
EmployeeID  FirstName                                          LastName                                           DepartmentID Salary  
----------- -------------------------------------------------- -------------------------------------------------- ------------ -----------  
1           John                                               Doe                                                10           60000  
2           Jane                                               Smith                                              20           75000  
3           Michael                                            Johnson                                            10           55000  
4           Emily                                              Brown                                              30           80000  
5           David                                              Wilson                                             20           70000  
6           Sarah                                              Garcia                                             10           62000  
7           Robert                                             Martinez                                           30           85000  

Simple CTE

A basic result set can be defined in a simple CTE that can be utilized in the initial query.

Example: Get all the employees in the Sales department.

;WITH SalesEmployees AS 
(  
SELECT  
EmployeeID, FirstName, LastName, Salary  FROM  Employees  
WHERE  DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales')  
)  
SELECT  EmployeeID, FirstName, LastName, Salary  FROM  SalesEmployees  
ORDER  BY  Salary DESC  

------OutPut  
EmployeeID  FirstName                                          LastName                                           Salary  
----------- -------------------------------------------------- -------------------------------------------------- -----------  
6           Sarah                                              Garcia                                             62000  
1           John                                               Doe                                                60000  
3           Michael                                            Johnson                                            55000  

Explanation:

The SalesEmployees CTE retrieves the employeeID, first name, last name and salary of an employee in the SalesEmployees table. The join that links the tables to the correct employees should be where the DepartmentID in the table employees is equated to the DepartmentID of the table Sales department. The primary query will retrieve all the fields of the SalesEmployees CTE and sort them order by salaries in an ascending order.

Several Other with CTE

A CTE may be referred to more than once in the same query

Find average salary of all employees and then find out employees whose salary is above the average.

;WITH EmployeeSalaries AS  
(  
SELECT  AVG(Salary)AS AverageSalary  
FROM  Employees  
)  
  
SELECT  EmployeeID,FirstName,LastName,Salary FROM  Employees e,EmployeeSalaries es  
WHERE  Salary>es.AverageSalary  

------ OutPut  
EmployeeID  FirstName                                          LastName                                           Salary  
----------- -------------------------------------------------- -------------------------------------------------- -----------  
2           Jane                                               Smith                                              75000  
4           Emily                                              Brown                                              80000  
5           David                                              Wilson                                             70000  
7           Robert                                             Martinez                                           85000  

Explanation:

• TheEmployeeSalaries CTE calculates the average salary of all employees.

• Themainqueryselectsemployeeswhosesalaryisgreaterthantheaveragesalary.

which is calculated at the EmployeeSalaries CTE.

CTE WITH JOINS

CTE scan incorporate joins in the merging of data in tables.

Example: Get the names of employees and the corresponding names of departments.

;WITH EmployeeDepartments AS  
(  
 SELECT  
 e.EmployeeID,e.FirstName, e.LastName,d.DepartmentName  
 FROM  Employees e  
 JOIN  Departments d ON e.DepartmentID=d.DepartmentID  
 )  
 SELECT  EmployeeID, FirstName, LastName, DepartmentName  FROM  EmployeeDepartments  
 ORDER BY  DepartmentName, LastName  

------ OutPut  
EmployeeID  FirstName                                          LastName                                           DepartmentName  
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------  
4           Emily                                              Brown                                              Engineering  
7           Robert                                             Martinez                                           Engineering  
2           Jane                                               Smith                                              Marketing  
5           David                                              Wilson                                             Marketing  
1           John                                               Doe                                                Sales  
6           Sarah                                              Garcia                                             Sales  
3           Michael                                            Johnson                                            Sales  

Explanation:

• EmployeeDepartments CTE is an action on combining Employees and Departments tables DepartmentID column.

• The primary question plucks out the EmployeeID, FirstName, LastName and DepartmentName the EmployeeDepartments CTE and sorts the output by department name and last name.

RECURSIVE CTE

Hierarchical data are queried through recursive CTE. They are done in two steps:

Anchor Member: The first SELECT statement that forms the base case.

Recursive Member: The SELECT string which uses the same CTE as the name of the CTE itself since it cannot be used to refer to itself directly run through the chain.

Example: (This example demands a table of self-reference, so, suppose we keep one) EmployeeHierarchy table).

-- Create EmployeeHierarchy  table    
  
CREATE TABLE EmployeeHierarchy 
(  
    EmployeeID INT PRIMARY KEY,  
    EmployeeName NVARCHAR(100),  
    ManagerID INT NULL  
);  

-- Insert into EmployeeHierarchy   
  
INSERT INTO EmployeeHierarchy (EmployeeID, EmployeeName, ManagerID)  
VALUES  
    (1, 'John Doe', NULL),        -- Top-level manager (no manager)  
    (2, 'Jane Smith', 1),         -- Reports to John  
    (3, 'Mike Brown', 1),         -- Reports to John  
    (4, 'Emily White', 2),        -- Reports to Jane  
    (5, 'David Black', 2);        -- Reports to Jane  

------Departments Rable OutPut  
EmployeeID  EmployeeName                                                                                         ManagerID  
----------- ---------------------------------------------------------------------------------------------------- -----------  
1           John Doe                                                                                             NULL  
2           Jane Smith                                                                                           1  
3           Mike Brown                                                                                           1  
4           Emily White                                                                                          2  
5           David Black                                                                                          2  

WITH  EmployeeHierarchyCTE AS   
(-- Anchor member: Select the top-level employee (John Doe)  
 SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level  
 FROM EmployeeHierarchy  WHERE ManagerID IS NULL  
 UNION ALL  
 -- Recursive member: Select subordinates of the current level  
 SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1  
 FROM EmployeeHierarchy e  
 JOIN EmployeeHierarchyCTE eh ON e.ManagerID = eh.EmployeeID  
 )  
 SELECT EmployeeID, EmployeeName, Level FROM EmployeeHierarchyCTE  
 WHERE EmployeeID <> 1-- Exclude John Doe himself  
 ORDER BY Level, EmployeeName  

------ OutPut  
EmployeeID  EmployeeName                                                                                         Level  
----------- ---------------------------------------------------------------------------------------------------- -----------  
2           Jane Smith                                                                                           1  
3           Mike Brown                                                                                           1  
5           David Black                                                                                          2  
4           Emily White                                                                                          2  

Explanation:

The top-level employee (John Doe) having NULL ManagerID is chosen by anchor, which is the member.
The recursive member relates the EmployeeHierarchy table to EmployeeHierarchyCTE
e.ManagerID = eh.EmployeeID, so doing the same as finding the subordinates of every employee. it is at its present level.
The UNION ALL aggregates both the anchor members and the recursive ones.
The primary query chosen is EmployeeID, EmployeeName, and Level out of the EmployeeHierarchyCTE and sorts results by level and name of employee.

CTE and Window Functions

CTEs may be applied together with window functions to make those calculations over a set

third person opinion in connection with row being spoken of.

Examples: Determine the wage per worker and his/her departmental ranking.

WITH RankedSalaries AS (  
SELECT  
EmployeeID, FirstName, LastName, DepartmentID, Salary  
,RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS  SalaryRank  
FROM  Employees  
)  
SELECT  
EmployeeID, FirstName, LastName, DepartmentID, Salary, SalaryRank  FROM  RankedSalaries  
ORDER BY  DepartmentID, SalaryRank  

------ OutPut  
EmployeeID  FirstName                                          LastName                                           DepartmentID Salary      SalaryRank  
----------- -------------------------------------------------- -------------------------------------------------- ------------ ----------- --------------------  
6           Sarah                                              Garcia                                             10           62000       1  
1           John                                               Doe                                                10           60000       2  
3           Michael                                            Johnson                                            10           55000       3  
2           Jane                                               Smith                                              20           75000       1  
5           David                                              Wilson                                             20           70000       2  
7           Robert                                             Martinez                                           30           85000       1  
4           Emily                                              Brown                                              30           80000       2  

Conclusion:

The Common Table Expressions are an excellent method of making SQL more legible. They allow you to break long, difficult to read, steps into more easily understandable named results sets. Due to that, CTEs make your code more readable, correct, and maintainable. By considering the issue well, it is possible to enhance SQL knowledge by using CTEs. It is just important to keep in mind that CTEs are slower under very complex queries. That way, intelligently use them to keep your request quick. CTEs have the capability to significantly clean up and organize your SQL codes in the long term.