AP 38 - Avoid nested CTEs

Severity

Type

Problem

It is possible to nest CTEs, but that does not mean you should write queries with them. Nesting CTEs can make the SQL query harder to read and understand, especially when multiple levels of nesting are involved.

WITH CTE1 AS (
    WITH CTE2 AS (
        SELECT col1 FROM table1 WHERE col1 > 10
    )
    SELECT col1 FROM CTE2 WHERE col1 < 50
)
SELECT * FROM CTE1;

Solution

Instead of nesting CTEs, list them sequentially in the WITH clause. This makes the query more readable and easier to maintain.

WITH CTE2 AS (
    SELECT col1 FROM table1 WHERE col1 > 10
),
CTE1 AS (
    SELECT col1 FROM CTE2 WHERE col1 < 50
)
SELECT * FROM CTE1;

Legitimate use of the anti pattern

None