Common Table Expression CTE in SQL – The WITH clause in MySQL is used to specify a Common Table Expression.

CTE in SQL:

A CTE is the result set of a query which exists temporarily and for use only within the context of a larger query. Much like a derived table, the result of a CTE is not stored and exists only for the duration of the query.

Uses of CTE:

It allows us to work with data specifically within that query, such as using it in SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, OR MERGE statements.

CTE Syntax:

WITH cte_name AS (
SELECT query
)
SELECT *
FROM cte_name;
In the above syntax:

cte_name: A unique name for the CTE expression.
query: A valid SQL query that returns a result set, which will be treated as a virtual table within the main query.
SELECT: The main query that can reference the CTE by its name.

Why do we need CTE?

Readability: Enhances code readability by breaking down complex queries into modular parts, making them easier to understand.
Reuse: Allows the reuse of the defined CTE within the same query, reducing redundancy and promoting code efficiency.
Recursive Queries: Facilitates the creation of recursive queries for scenarios like hierarchical data representation, which can be challenging with traditional approaches.
Code Maintenance: Simplifies code maintenance by isolating logical units of work, making it easier to modify or extend queries without affecting the entire codebase.
Optimization: May help the query optimizer to better optimize the execution plan, potentially leading to improved performance in certain scenarios.
Self-Referencing Queries: Enables self-referencing queries where a CTE can reference itself, supporting scenarios like hierarchical or graph-based data representations.

CTE from Multiple Tables:

We can also create a Common Table Expression (CTE) that combines data from multiple tables by using JOIN operations within the CTE’s subquery. To do this, we need to use the comma operator to separate each CTE definition, effectively merging them into a single statement.

Syntax
Following is the basic syntax for multiple Common Table Expression (CTE) −

WITH
CTE_NAME1 (column_name) AS (query),
CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;
We can use multiple Common Table Expressions (CTEs) with various SQL operations, such as UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT.

Recursive Common Table Expression:

A recursive CTE references itself and is useful for querying hierarchical data, such as employees and their managers stored in the same table. It repeatedly executes until the full hierarchy is returned. To avoid infinite loops from incorrect definitions, use the MAXRECURSION hint in the query’s OPTION clause.

Recursive CTEs consist of two parts:

Anchor member: The initial query that selects the base case (e.g., top-level managers).
Recursive member: The query that references the CTE itself, pulling the next level of data.

Recursive CTE Syntax:

WITH RECURSIVE [cte name] AS (
[base case]
UNION ALL
[recursive term]
)
[primary query]

A recursive CTE consists of a base case and a recursive term. The base case defines the starting point of the recursive query (e.g., the first node in a graph or the parent in a hierarchy). The recursive term describes the “loop” that will run until some endpoint is determined.

This recursive term will reference the CTE itself to create the loop. The base case and recursive term are combined using a UNION ALL (or sometimes just UNION if you want to eliminate duplicates, but be cautious as it can be more expensive in terms of performance) to create the full recursive CTE.

Recursive CTE Example:

WITH RECURSIVE employee_hierarchy(id, name, manager_id, level) AS (
SELECT id, name, manager_id, 1 — level starting at one
FROM employees
WHERE id = 5

UNION ALL

SELECT manager.id, manager.name, manager.manager_id, level+1 — increment level
FROM employees manager
JOIN employee_hierarchy employee ON employee.manager_id = manager.id
)
SELECT * FROM employee_hierarchy ORDER BY level DESC;

What is CTE Mssql?

CTE (Common Table Expression) in MSSQL is a named temporary result set defined within a SELECT, INSERT, UPDATE, or DELETE statement for improved query readability.

WITH EmployeeDepartment AS
(
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
)
SELECT *
FROM EmployeeDepartment
WHERE DepartmentName = ‘IT’;

How do I create a CTE?

Use the WITH keyword followed by the CTE name and SELECT statement. For recursive CTE, add the RECURSIVE keyword.

What are the benefits of CTE in SQL?

The benefits of CTE in SQL include improves query readability, encourages code reuse, facilitates recursive queries, and simplifies code maintenance in SQL.

Is CTE better than temp table?

CTEs are advantageous for readability and reuse, while temp tables may be better for larger datasets and persistent storage, depending on specific use cases.

Combine CTE with DELETE:

WITH DuplicateUsers AS
(
SELECT
UserID,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY UserID) AS rnk_
FROM Users
)
DELETE
FROM DuplicateUsers
WHERE rnk_ > 1;