SQL

CTE (WITH Clause)

Improving query readability with Common Table Expressions

By TechCoder TeamLast updated: 2026-06-02
In a Nutshell

Improving query readability with Common Table Expressions This hands-on tutorial focuses on practical implementation of cte (with clause) concepts.

What is a CTE?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries more readable.

Syntax

WITH CteName AS (
    SELECT ...
)
SELECT * FROM CteName;

Example

WITH HighSalaries AS (
    SELECT * FROM Employees WHERE Salary > 50000
)
SELECT * FROM HighSalaries WHERE Department = 'IT';
sql-basic-cte

Using CTE

Problem Statement

Refactor this subquery into a CTE named RecentOrders: SELECT * FROM (SELECT * FROM Orders WHERE Date > '2023-01-01') AS RecentOrders.

Recursive CTE

A CTE that references itself. Useful for hierarchical data (e.g., organizational charts).

WITH RECURSIVE CategoryPath AS (
    -- Anchor member
    SELECT ID, Name, ParentID FROM Categories WHERE ParentID IS NULL
    UNION ALL
    -- Recursive member
    SELECT c.ID, c.Name, c.ParentID 
    FROM Categories c
    INNER JOIN CategoryPath cp ON c.ParentID = cp.ID
)
SELECT * FROM CategoryPath;