How CTE (WITH) works in SQL? Best CTE (WITH) examples

How CTE (WITH) works in SQL? Best CTE (WITH) examples
“`html

When working with SQL, we often encounter complex queries that require subqueries, temporary tables, or derived tables. One of the best ways to simplify these queries and improve readability is by using Common Table Expressions (CTEs) with the WITH statement. Let’s dive deep into how CTEs work, why they are useful, and see some of the best examples of using them effectively.

What is a CTE?

A Common Table Expression (CTE) is a temporary result set that exists only during the execution of a single SELECT, INSERT, UPDATE, or DELETE statement. It allows us to organize and structure queries in a more readable way.

Why Use a CTE?

CTEs provide several advantages:

  • Improves Readability: Makes SQL queries more structured and easier to follow.
  • Encapsulates Logic: Helps break down complex queries into manageable parts.
  • Can be Referenced Multiple Times: Eliminates code duplication by allowing reuse of the same logic.
  • Enhances Performance: Optimizes execution by reducing redundant subquery calculations.

Basic Syntax of a CTE

The standard syntax for defining a CTE is as follows:

WITH cte_name AS (
    -- CTE definition (Subquery)
    SELECT column1, column2
    FROM some_table
    WHERE some_condition
)
SELECT * FROM cte_name;

The WITH clause defines the CTE, which can then be referenced within the main query.

Best CTE (WITH) Examples

1. Using a CTE for Simple Query Optimization

Imagine you have a table of employees and want to filter employees who earn more than the average salary.

WITH AvgSalary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT avg_sal FROM AvgSalary);

This approach improves readability compared to an inline subquery.

2. Recursive CTE for Hierarchical Data

CTEs are especially useful for working with hierarchical data like organizational structures or category trees.

WITH RecursiveHierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, rh.level + 1
    FROM employees e
    JOIN RecursiveHierarchy rh ON e.manager_id = rh.id
)
SELECT * FROM RecursiveHierarchy;

This recursive query retrieves an entire employee hierarchy while maintaining hierarchy depth.

3. Using Multiple CTEs in a Single Query

We can define multiple CTEs at the same time and use them together in one query.

WITH HighSalary AS (
    SELECT name, salary FROM employees WHERE salary > 70000
),
LowSalary AS (
    SELECT name, salary FROM employees WHERE salary <= 70000
)
SELECT * FROM HighSalary
UNION
SELECT * FROM LowSalary;

This method is useful when dealing with multiple data subsets.

4. CTE with Aggregation

CTEs can also be used to create summary data, like calculating total sales per department.

WITH DepartmentSales AS (
    SELECT department_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY department_id
)
SELECT d.name AS department_name, ds.total_sales
FROM DepartmentSales ds
JOIN departments d ON ds.department_id = d.id;

This approach allows us to separate calculations from the final query result.

Performance Considerations

While CTEs improve readability, they don’t always improve performance. Some key considerations:

  • Materialized CTEs: In some databases (like SQL Server), the optimizer may materialize a CTE, which can impact performance.
  • Indexes: Query performance may still depend heavily on indexes.
  • Repeated Execution: If the CTE is used multiple times in the same query, it may be better to use a temporary table.

Difference Between CTE, Subquery, and Temporary Table

Feature CTE Subquery Temporary Table
Scope Limited to a single query Part of the main query Exists for the session
Readability Best for readability Can be messy in complex queries Useful for intermediate data
Performance Depends on execution plan May be optimized better Can have indexes

Final Thoughts

CTEs in SQL enhance query structuring, making them more readable and manageable. While their performance benefits depend on the database engine and indexing strategies, they remain a crucial tool for optimizing complex queries. By mastering CTEs, you can write cleaner, more efficient SQL queries while avoiding redundant subqueries and temporary tables.

``` Other interesting article: How PARTITION BY works in SQL? Best PARTITION BY examples