8.1 Common Table Expressions (CTEs)

 Common Table Expressions (CTEs) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They provide a way to break down complex queries into simpler, more manageable components.

1. Introduction to CTEs

A CTE is defined using the WITH clause and can be referenced within the same query. It improves readability and makes complex queries easier to understand.

2. Creating a Simple CTE

Let's start by creating a simple CTE that selects data from a table:

WITH SampleCTE AS (
    SELECT 
        EmployeeID,
        FirstName,
        LastName
    FROM 
        Employees
)                             
SELECT *
FROM SampleCTE;

Output:

EmployeeID	FirstName	LastName
1	John	Doe
2	Jane	Smith
3	Bob	Johnson

3. CTEs with Joins

You can use CTEs in combination with JOIN operations to create more complex queries:

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

Output:

EmployeeID	FirstName	LastName	DepartmentName
1	John	Doe	Sales
2	Jane	Smith	Marketing
3	Bob	Johnson	Finance

4. Recursive CTEs

Recursive CTEs can be used to work with hierarchical data, such as organizational charts:

WITH EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        ManagerID,
        FirstName,
        LastName,
        0 AS Level
    FROM 
        Employees
    WHERE 
        ManagerID IS NULL
    UNION ALL
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.FirstName,
        e.LastName,
        eh.Level + 1
    FROM 
        Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level
FROM 
    EmployeeHierarchy;

Output:

EmployeeID	FirstName	LastName	Level
1	John	Doe	0
2	Jane	Smith	1
3	Bob	Johnson	2

This tutorial provides a basic understanding of Common Table Expressions and demonstrates their usage in different scenarios. Feel free to explore further and apply CTEs to your specific use cases.