Using common table expressions in SQL SELECT statements

18 Sep 2023

Common Table Expressions, or CTEs, are a powerful feature in SQL that makes complex queries more readable and manageable. CTEs allow you to define temporary result sets, known as “common tables,” which can then be used within the same SELECT statement or subsequent statements. In this blog post, we will explore how to use CTEs in SQL SELECT statements.

Syntax and Structure

The syntax and structure of a CTE in a SQL SELECT statement involve two main components: the WITH clause and the subsequent SELECT statement. Here’s an example:

WITH cte_name (column_list) AS (
    -- CTE query definition
    SELECT column1, column2, ...
    FROM table
    WHERE conditions
)
SELECT column_list
FROM cte_name
JOIN other_table ON cte_name.column = other_table.column;

In this example, the WITH clause is used to define the CTE named cte_name. The column list in parentheses specifies the names of the columns that will be referenced in the subsequent SELECT statement.

The CTE query definition follows the AS keyword and includes a SELECT statement that retrieves data from a table or multiple tables. You can apply any filtering or transformation logic within this query.

Finally, the main SELECT statement references the CTE by its name (cte_name) and can join it with other tables as needed.

Benefits of Using CTEs

Code readability: CTEs allow you to break down complex queries into smaller, more manageable parts, making your code easier to understand and maintain.
Code reuse: CTEs can be used multiple times within the same SQL statement, eliminating the need to repetitively write the same subquery logic.
Performance optimization: CTEs can improve query performance by allowing the optimizer to treat them as materialized views, effectively caching the result set for reuse.

Examples

Let’s look at a few examples to understand how CTEs can be used in SQL SELECT statements.

Example 1: Recursive CTE

A recursive common table expression is useful when dealing with hierarchical data. Consider a table employees with columns employee_id and manager_id, representing a hierarchical structure of employees and their managers. We can use a recursive CTE to retrieve all employees under a specific manager:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE manager_id = 123
    UNION ALL
    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;

Example 2: Derived CTE

A derived common table expression is useful when you need to perform complex calculations or transformations on a subset of your data before using it in the main query. Consider a table orders with columns order_id, order_date, and total_amount. We can use a derived CTE to calculate the average order amount for each month:

WITH monthly_avg_orders AS (
    SELECT DATE_TRUNC('month', order_date) AS month,
           AVG(total_amount) AS avg_order_amount
    FROM orders
    GROUP BY month
)
SELECT month, avg_order_amount
FROM monthly_avg_orders
ORDER BY month;

Conclusion

Common Table Expressions provide a convenient way to break down complex SQL queries into smaller, reusable parts. By using CTEs in SQL SELECT statements, you can enhance code readability, promote code reuse, and improve query performance. Whether you’re working with hierarchical data or need to perform complex calculations, CTEs can be a valuable tool in your SQL toolbox.

#SQL #CTE