Common Table Expressions (CTE)

Created: 2023-08-17 13:50

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

CTEs are defined WITH keyword. Can be used to break down complex queries into simpler pieces, enable recursive queries, or reusing the result set multiple times in a main query.

Here’s the basic syntax for a CTE:

WITH cte_name (column_name1, column_name2, ...)
AS (
    -- CTE query goes here
)
-- Main query that can reference the CTE goes here

Key points about CTEs: §

1. Readability: CTEs can make queries more readable. Instead of having one large query or subquery, it can be broken up into smaller, named chunks.
2. Reuse: Within the main statement, the CTE can be referred multiple times, avoiding the need to recreate or copy the same subquery.
3. Recursion: CTEs can be recursive. A recursive CTE is defined by repeatedly executing the CTE to return subsets of data until the complete result set is obtained. Especially useful for hierarchical data structures, like fetching an entire tree or hierarchy.
4. Scope: The CTE is only scoped to the query in which it’s defined. As opposed to temporary tables that can be referenced it in multiple separate queries.

Examples: §

1. Simple CTE:

WITH CTE_Sample AS (
    SELECT first_name, last_name 
    FROM employees 
    WHERE department_id = 10
)
SELECT * FROM CTE_Sample;

2. Recursive CTE: the table named hierarchy has id and parent_id columns to represent a simple tree structure. A recursive CTE can retrieve all descendants of a node.

WITH RECURSIVE descendants AS (
    SELECT id, parent_id
    FROM hierarchy
    WHERE parent_id = 1  -- starting point
    
    UNION ALL
    
    SELECT h.id, h.parent_id
    FROM hierarchy h
    JOIN descendants d ON h.parent_id = d.id
)
SELECT * FROM descendants;

The CTE fetches all nodes (and their descendants) that have a parent_id of 1.

Here’s another example => Query hierarchical data with a recursive CTE