Simplify SQL Queries with Common Table Expressions (CTEs)

Table of Contents

1. Introduction and definition of CTEs

• 1.1 What is a CTE?
• 1.2 Basic syntax of a CTE
• 1.3 CTEs vs Subqueries

2. Demo: Simplify complex queries with CTEs

• 2.1 Setting up the demo environment
• 2.2 Refactoring a subquery in the SELECT
• 2.3 Refactoring a subquery in the FROM
• 2.4 Refactoring a subquery in the WHERE / HAVING
• 2.5 Reuse of a CTE — avoid repetition

3. Demo: Recursive CTEs for hierarchical data

• 3.1 Structure of a Recursive CTE
• 3.2 Setting up the employee-manager hierarchy
• 3.3 Anchor Member and Recursive Member
• 3.4 Limit recursion depth

4. Comparison of CTEs according to SQL platforms

• 4.1 Support for CTEs — SQL Server, PostgreSQL, MySQL
• 4.2 Performance considerations

5. Demo files

• 5.1 1-CTEs.sql — Refactoring of subqueries
• 5.2 2-RecursiveCTEs.sql — Employee hierarchy

6. Summary and good practices

1. Introduction and definition of CTEs

1.1 What is a CTE?

A CTE (Common Table Expression) is a temporary result set that can be used inside a SELECT, INSERT, UPDATE or DELETE query. CTEs are comparable to subqueries, but with increased flexibility.

Main goal: simplify complex queries by breaking them down into modular steps that are more readable and easier to maintain.

Basic characteristics:

• A CTE is defined only once in the query and can be referenced multiple times.
• It only exists during the execution of the query that contains it — it is not persisted in the database.
• CTEs can be recursive, which makes them particularly useful for processing hierarchical data or repetitive operations.

1.2 Basic syntax of a CTE

The syntax of a CTE always begins with the keyword WITH, followed by the name of the CTE, then the query definition in parentheses.

WITH cte_name AS (
    -- Requête qui définit le CTE
    SELECT colonne1, colonne2
    FROM ma_table
    WHERE condition
)
-- Requête principale qui utilise le CTE
SELECT *
FROM cte_name;

Line by line explanation:

Multiple CTEs: it is possible to define several CTEs in the same WITH clause, separated by commas:

WITH CTE1 AS (
    SELECT ...
),
CTE2 AS (
    SELECT ... FROM CTE1  -- Un CTE peut référencer un CTE défini avant lui
)
SELECT * FROM CTE2;

1.3 CTEs vs Subqueries

subqueries are defined directly in SELECT or WHERE clauses, making them more difficult to read and reuse. For simple and one-off requests, they remain acceptable. As complexity increases, CTEs provide a significant advantage.

2. Demo: Simplifying complex queries with CTEs

2.1 Setting up the demo environment

The demo uses a sales_demo database with a sales table that stores sales data by region, by seller, and by product category.

-- Création de la base de données et de la table
CREATE DATABASE IF NOT EXISTS sales_demo;
USE sales_demo;

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(50),
    salesperson VARCHAR(100),
    product_category VARCHAR(50),
    sales_amount DECIMAL(10, 2),
    sales_date DATE
);

-- Insertion des données d'exemple
INSERT INTO sales (region, salesperson, product_category, sales_amount, sales_date) VALUES
('North', 'Alice', 'Electronics', 5000, '2024-01-10'),
('North', 'Bob', 'Furniture', 3000, '2024-01-12'),
('South', 'Charlie', 'Electronics', 7000, '2024-01-15'),
('South', 'David', 'Furniture', 2000, '2024-01-20'),
('West', 'Eve', 'Electronics', 4500, '2024-02-01'),
('West', 'Frank', 'Furniture', 3500, '2024-02-03');

Structure of the sales table:

2.2 Refactoring a subquery in SELECT

Problem: calculate the total sales of the Electronics category for each seller. The version with subquery uses a correlation in the SELECT, which becomes difficult to read and maintain as complexity increases.

Before — with subquery in SELECT:

SELECT 
    salesperson,
    (SELECT SUM(sales_amount) 
     FROM sales s2 
     WHERE s2.salesperson = s1.salesperson 
     AND s2.product_category = 'Electronics') AS electronics_sales
FROM sales s1
GROUP BY salesperson;

The correlated subquery is executed for each row of the outer query result, which can cause performance issues on large tables.

After — refactored with a CTE:

WITH ElectronicsSales AS (
    SELECT 
        salesperson, 
        SUM(sales_amount) AS electronics_sales
    FROM sales
    WHERE product_category = 'Electronics'
    GROUP BY salesperson
)
SELECT 
    s.salesperson,
    es.electronics_sales
FROM sales s
LEFT JOIN ElectronicsSales es
ON s.salesperson = es.salesperson;

Advantages of this refactoring:

• The CTE ElectronicsSales is calculated once and then attached to the main query.
• Electronics sales calculation logic is clearly separated from the join logic.
• The main query is concise and clearly expresses the intent.

2.3 Refactoring a subquery in FROM

Problem: Find the best seller by region by first calculating sales totals by region and seller.

Before — with subquery in FROM:

SELECT 
    region, 
    salesperson AS top_salesperson
FROM (
    SELECT 
        region, 
        salesperson, 
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, salesperson
) AS regional_sales
ORDER BY total_sales DESC;

Subqueries in the FROM (also called derived tables) are difficult to read when they accumulate or when the external query must reference them in several places.

After — refactored with a CTE:

WITH RegionalSales AS (
    SELECT 
        region, 
        salesperson, 
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, salesperson
)
SELECT 
    region, 
    salesperson AS top_salesperson
FROM RegionalSales
ORDER BY total_sales DESC;

Advantages:

• The CTE RegionalSales replaces the derived table and gives it an explicit name.
• The main query is more linear and easier to read.
• If total_sales is needed in other parts of the query, the CTE can be reused without repetition.

2.4 Refactoring a subquery in the WHERE / HAVING

Problem: Filter sellers whose total sales are greater than the average sales.

Before — with subquery in the HAVING:

SELECT 
    salesperson, 
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson
HAVING SUM(sales_amount) > (
    SELECT AVG(sales_amount) FROM sales
);

Averaging is buried in the HAVING clause, making the intent less obvious.

After — refactored with a CTE:

WITH AvgSales AS (
    SELECT AVG(sales_amount) AS avg_sales FROM sales
)
SELECT 
    s.salesperson, 
    SUM(s.sales_amount) AS total_sales
FROM sales s
GROUP BY s.salesperson
HAVING total_sales > (SELECT avg_sales FROM AvgSales);

Advantages:

• Averaging logic is extracted into CTE AvgSales.
• The intent of the HAVING clause becomes immediately readable.

2.5 Reuse of a CTE — avoid repetition

One of the most important advantages of CTEs is the ability to define logic once and reuse it multiple times in the same query, thereby eliminating code duplication.

Problem: Calculate the total sales per seller, then compare each seller to the average of those totals. Without CTE, the calculation of totals per seller must be repeated three times in the same query.

Before — subquery repeated 3 times:

SELECT 
    s.salesperson,
    s.total_sales,
    (SELECT AVG(total_sales) 
     FROM (SELECT salesperson, SUM(sales_amount) AS total_sales 
           FROM sales GROUP BY salesperson) AS avg_sales_subquery) AS avg_sales
FROM 
    (SELECT salesperson, SUM(sales_amount) AS total_sales 
     FROM sales GROUP BY salesperson) AS s
WHERE 
    s.total_sales > (SELECT AVG(total_sales) 
                     FROM (SELECT salesperson, SUM(sales_amount) AS total_sales 
                           FROM sales GROUP BY salesperson) AS avg_sales_subquery);

This code is difficult to read, maintain, and potentially inefficient because the same subcalculation can be executed multiple times.

After — refactored with two CTEs:

WITH SalesTotals AS (
    SELECT 
        salesperson, 
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson
),
AvgSales AS (
    SELECT AVG(total_sales) AS avg_sales FROM SalesTotals
)
SELECT 
    s.salesperson,
    s.total_sales,
    a.avg_sales
FROM SalesTotals s, AvgSales a
WHERE 
    s.total_sales > a.avg_sales;

Key points from this example:

• SalesTotals is set only once and referenced in AvgSales as well as in the main query.
• AvgSales built on SalesTotals, illustrating how CTEs can be chained.
• The main query expresses the business intent clearly: display sellers whose sales exceed the average.
• Code is reduced from ~15 duplicate lines to a single, reusable definition.

3. Demo: Recursive CTEs for hierarchical data

3.1 Structure of a Recursive CTE

A Recursive CTE is a CTE that references itself in its own definition. It is particularly suitable for hierarchical data structures such as:

• Organizational hierarchies (employees / managers)
• Tree structures (categories / subcategories)
• Dependency graphs
• Numerical sequences or sequences

General syntax of a Recursive CTE:

WITH RECURSIVE cte_name AS (
    -- 1. Anchor Member : point de départ de la récursion
    SELECT colonnes
    FROM table
    WHERE condition_initiale

    UNION ALL

    -- 2. Recursive Member : jointure du CTE avec lui-même
    SELECT colonnes
    FROM table
    INNER JOIN cte_name ON condition_de_jointure
)
SELECT * FROM cte_name;

The two mandatory parts of a Recursive CTE:

Stopping condition: the recursion stops naturally when the Recursive Member no longer returns any rows (the join no longer finds matches).

Warning: without a stopping condition or with cyclical data, a Recursive CTE can generate an infinite loop. Most SQL engines impose a default recursion limit (e.g. 100 levels in SQL Server).

3.2 Establishment of the employee-manager hierarchy

The demo creates a CompanyDB database with an employees table which represents a typical organizational structure.

-- Création de la base de données
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Création de la table des employés
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

-- Insertion des données hiérarchiques
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'CEO',                 NULL),   -- Pas de manager : sommet de la hiérarchie
(2, 'CFO',                 1),      -- Rapporte au CEO
(3, 'CTO',                 1),      -- Rapporte au CEO
(4, 'VP of Finance',       2),      -- Rapporte au CFO
(5, 'VP of Engineering',   3),      -- Rapporte au CTO
(6, 'Finance Manager',     4),      -- Rapporte au VP of Finance
(7, 'Engineering Manager', 5),      -- Rapporte au VP of Engineering
(8, 'Software Engineer',   7),      -- Rapporte à l'Engineering Manager
(9, 'Data Scientist',      7);      -- Rapporte à l'Engineering Manager

Visual representation of the hierarchy:

CEO (level 1)
├── CFO (level 2)
│   └── VP of Finance (level 3)
│       └── Finance Manager (level 4)
└── CTO (level 2)
    └── VP of Engineering (level 3)
        └── Engineering Manager (level 4)
            ├── Software Engineer (level 5)
            └── Data Scientist (level 5)

Data model:

3.3 Anchor Member and Recursive Member

The following Recursive CTE goes through the entire hierarchy starting from the CEO (whose manager_id is NULL) and goes down level by level.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member : point de départ = sommet de la hiérarchie (CEO)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level          -- Le CEO est au niveau 1
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive Member : cherche les employés qui rapportent aux employés déjà trouvés
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        eh.level + 1 AS level   -- Chaque niveau descend d'un cran
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
-- Requête finale : afficher toute la hiérarchie triée par niveau
SELECT * 
FROM EmployeeHierarchy
ORDER BY level;

Execution flow step by step:

Important points:

• The Anchor Member filters WHERE manager_id IS NULL to find the top(s) of the hierarchy.
• The Recursive Member joins employees with EmployeeHierarchy on e.manager_id = eh.employee_id, which means: “finds all employees whose manager is already in the previous results”.
• The UNION ALL operator combines each iteration into the cumulative result set.
• The level field is incremented at each iteration (eh.level + 1), allowing us to know the depth of each employee in the hierarchy.

3.4 Limit recursion depth

In certain cases, you want to limit the depth of the recursion — for example to only display the first three management levels.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        eh.level + 1 AS level
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
    WHERE eh.level < 3  -- Limite la récursion à 3 niveaux
)
SELECT * 
FROM EmployeeHierarchy
ORDER BY level;

Explanation:

• The condition WHERE eh.level < 3 is added in the Recursive Member.
• This prevents CTE from continuing to search for sublevels beyond level 3.
• Only the CEO (level 1), the CFO and the CTO (level 2), as well as the VPs (level 3) will be included in the results.

Common use cases for depth limitation:

• Show only the first N levels of a category tree.
• Protect against accidental loops in poorly structured graphs.
• Optimize performance by avoiding unnecessarily deep recursion.

4. Comparison of CTEs according to SQL platforms

4.1 Support for CTEs — SQL Server, PostgreSQL, MySQL

The three major SQL platforms support CTEs, but with important nuances, particularly for recursive CTEs.

Nuance on the syntax of Recursive CTEs:

• SQL Server: just uses WITH cte_name AS (...) — the RECURSIVE keyword is not necessary.
• PostgreSQL: uses WITH RECURSIVE cte_name AS (...) — the RECURSIVE keyword is mandatory for recursive CTEs.
• MySQL: from version 8.0, uses WITH RECURSIVE cte_name AS (...). Before version 8.0, CTEs were not supported.

Attention during migration: if a recursive CTE is developed on SQL Server (without RECURSIVE) and must be ported to PostgreSQL or MySQL, the keyword RECURSIVE must be added to the WITH clause.

4.2 Performance considerations

SQL engines can process CTEs in two different ways, which impacts performance:

Important points:

• non-recursive CTEs are generally transparently optimized by modern platforms, but the exact behavior is engine dependent.
• SQL Server materializes CTEs by default in certain cases (notably with OPTION (MAXRECURSION N) to control the recursion limit).
• PostgreSQL introduced in version 12 the ability to explicitly control materialization with WITH cte AS MATERIALIZED (...) or WITH cte AS NOT MATERIALIZED (...).
• MySQL 8.0 optimizes non-recursive CTEs by generally inlining them, but recursive CTEs are always materialized.

Practical recommendations:

• Use EXPLAIN (or EXPLAIN ANALYZE on PostgreSQL) to analyze the execution plan and check if the CTE is inlined or materialized.
• For CTEs used only once in a simple query, performance is generally equivalent to that of a subquery.
• For CTEs reused several times, materialization can offer an advantage by avoiding recalculating the same result.
• Pay particular attention to the recursion limit — on SQL Server it is 100 by default and can be changed with OPTION (MAXRECURSION N).

5. Demo files

5.1 1-CTEs.sql — Refactoring subqueries

File: 01/demos/1-CTEs.sql

This file illustrates the progressive refactoring of subqueries into CTEs in three different contexts: in the SELECT, in the FROM and in the WHERE/HAVING. It also demonstrates the reuse of multiple CTEs to avoid code duplication.

-- Create the Database and Table
-- This creates a new database 'sales_demo' and a table 'sales' to store region-wise sales data for various salespeople.
CREATE DATABASE IF NOT EXISTS sales_demo;
USE sales_demo;

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(50),
    salesperson VARCHAR(100),
    product_category VARCHAR(50),
    sales_amount DECIMAL(10, 2),
    sales_date DATE
);

-- Insert sample data
-- Populates the 'sales' table with some example sales records.
INSERT INTO sales (region, salesperson, product_category, sales_amount, sales_date) VALUES
('North', 'Alice', 'Electronics', 5000, '2024-01-10'),
('North', 'Bob', 'Furniture', 3000, '2024-01-12'),
('South', 'Charlie', 'Electronics', 7000, '2024-01-15'),
('South', 'David', 'Furniture', 2000, '2024-01-20'),
('West', 'Eve', 'Electronics', 4500, '2024-02-01'),
('West', 'Frank', 'Furniture', 3500, '2024-02-03');

-- Subquery in SELECT (Before Refactoring)
-- Calculates the total sales for each salesperson for the 'Electronics' category using a subquery in the SELECT clause.
SELECT 
    salesperson,
    (SELECT SUM(sales_amount) 
     FROM sales s2 
     WHERE s2.salesperson = s1.salesperson 
     AND s2.product_category = 'Electronics') AS electronics_sales
FROM sales s1
GROUP BY salesperson;

-- Refactoring Subquery in SELECT to a CTE
-- Refactors the previous query to use a CTE for 'electronics_sales' and joins it in the main query for clarity.
WITH ElectronicsSales AS (
    SELECT 
        salesperson, 
        SUM(sales_amount) AS electronics_sales
    FROM sales
    WHERE product_category = 'Electronics'
    GROUP BY salesperson
)
SELECT 
    s.salesperson,
    es.electronics_sales
FROM sales s
LEFT JOIN ElectronicsSales es
ON s.salesperson = es.salesperson;

-- Subquery in FROM (Before Refactoring)
-- Calculates the total sales for each salesperson per region using a subquery in the FROM clause, then orders by total sales to get the top salesperson.
SELECT 
    region, 
    salesperson AS top_salesperson
FROM (
    SELECT 
        region, 
        salesperson, 
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, salesperson
) AS regional_sales
ORDER BY total_sales DESC;

-- Refactoring Subquery in FROM to a CTE
-- Refactors the previous query to use a CTE to calculate regional sales totals, improving readability.
WITH RegionalSales AS (
    SELECT 
        region, 
        salesperson, 
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, salesperson
)
SELECT 
    region, 
    salesperson AS top_salesperson
FROM RegionalSales
ORDER BY total_sales DESC;

-- Subquery in WHERE (Before Refactoring)
-- Calculates the total sales per salesperson and filters those whose total sales are above the average sales using a subquery in the HAVING clause.
SELECT 
    salesperson, 
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson
HAVING SUM(sales_amount) > (
    SELECT AVG(sales_amount) FROM sales
);

-- Refactoring Subquery in WHERE to a CTE
-- Refactors the previous query by using a CTE to calculate the average sales and references it in the HAVING clause.
WITH AvgSales AS (
    SELECT AVG(sales_amount) AS avg_sales FROM sales
)
SELECT 
    s.salesperson, 
    SUM(s.sales_amount) AS total_sales
FROM sales s
GROUP BY s.salesperson
HAVING total_sales > (SELECT avg_sales FROM AvgSales);

-- Original query with subquery used twice
-- Calculates total sales per salesperson and also calculates the average of those totals,
-- using the same subquery twice, once in SELECT and once in WHERE.
SELECT 
    s.salesperson,
    s.total_sales,
    (SELECT AVG(total_sales) 
     FROM (SELECT salesperson, SUM(sales_amount) AS total_sales FROM sales GROUP BY salesperson) AS avg_sales_subquery) AS avg_sales
FROM 
    (SELECT salesperson, SUM(sales_amount) AS total_sales FROM sales GROUP BY salesperson) AS s
WHERE 
    s.total_sales > (SELECT AVG(total_sales) 
                     FROM (SELECT salesperson, SUM(sales_amount) AS total_sales FROM sales GROUP BY salesperson) AS avg_sales_subquery);

-- Refactored query using a CTE to reuse the subquery
-- Refactors the previous query by using a CTE to calculate 'SalesTotals' and then calculates
-- the average sales, allowing the subquery to be reused instead of repeating it.
WITH SalesTotals AS (
    SELECT 
        salesperson, 
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson
),
AvgSales AS (
    SELECT AVG(total_sales) AS avg_sales FROM SalesTotals
)
SELECT 
    s.salesperson,
    s.total_sales,
    a.avg_sales
FROM SalesTotals s, AvgSales a
WHERE 
    s.total_sales > a.avg_sales;

-- Clean Up
-- Drops the 'sales_demo' database to clean up the environment after the demo is complete.
DROP DATABASE sales_demo;

5.2 2-RecursiveCTEs.sql — Employee hierarchy

File: 01/demos/2-RecursiveCTEs.sql

This file demonstrates the use of Recursive CTEs to traverse an organizational hierarchy. Two variants are presented: complete traversal of the hierarchy, then traversal limited to 3 levels.

-- Create the Database
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Create a table to store employee-manager relationships
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

-- Insert sample data for employees and managers
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CFO', 1),
(3, 'CTO', 1),
(4, 'VP of Finance', 2),
(5, 'VP of Engineering', 3),
(6, 'Finance Manager', 4),
(7, 'Engineering Manager', 5),
(8, 'Software Engineer', 7),
(9, 'Data Scientist', 7);

-- Recursive CTE to retrieve the employee hierarchy
WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: Start with the CEO (or top of the hierarchy)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member: Find employees who report to the current level of employees
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        eh.level + 1 AS level
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
-- Query the hierarchy, ordered by level
SELECT * 
FROM EmployeeHierarchy
ORDER BY level;

-- Recursive CTE with depth limit (3 levels)
WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        eh.level + 1 AS level
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
    WHERE eh.level < 3 -- Limit recursion to 3 levels
)
SELECT * 
FROM EmployeeHierarchy
ORDER BY level;

-- Clean Up
-- Drop the employees table after the demo
DROP TABLE employees;
DROP DATABASE CompanyDB;

6. Summary and best practices

When to use a CTE?

CTEs are particularly suitable in the following situations:

• Complex queries with multiple levels of logic: Each CTE represents an intermediate logical step, making the overall query easier to read and debug.
• Reuse of logic in several parts of the query: rather than repeating the same subquery, we define it once in a CTE and reference it several times.
• Hierarchical data: Recursive CTEs are the natural solution for traversing tree structures or dependency graphs.
• Legacy SQL Refactoring: Replacing complex subqueries with CTEs is a refactoring step that improves maintainability without changing functional behavior.

Best practices

Quick comparison — CTE vs Subquery vs Temporary Table

Summary of key concepts

• A CTE is defined with the clause WITH cte_name AS (...) and represents a temporary dataset reusable in the same query.
• CTEs improve readability, maintainability and can improve performance by avoiding duplicate calculations.
• Recursive CTEs consist of an Anchor Member (starting point) and a Recursive Member (iteration), connected by UNION ALL.
• All three major SQL platforms (SQL Server, PostgreSQL, MySQL 8.0+) support CTEs, with syntactic nuances for recursive CTEs.
• The SQL engine may choose to inline or materialize a CTE — understanding this behavior is important for performance optimization.

Search Terms

simplify · sql · queries · expressions · ctes · fundamentals · databases · cte · refactoring · subquery · recursive · comparison · hierarchy · subqueries