The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Common Table Expressions (CTEs)

Next ►
►

Introduction

Common Table Expressions (CTEs) are a powerful feature in SQL that allow you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve the readability and maintainability of complex queries by breaking them down into simpler, more manageable parts.

Key Concepts

  • CTE Definition: A CTE is defined using the WITH keyword followed by a query that generates a result set.
  • Temporary Nature: CTEs are temporary and only exist during the execution of the query.
  • Recursive CTEs: CTEs can be recursive, allowing you to perform operations like hierarchical data traversal.

Syntax

The basic syntax for a CTE is as follows:

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

Practical Example

Let's consider a simple example where we want to find the average salary of employees in each department.

Step-by-Step Explanation

  1. Define the CTE: We create a CTE named department_salaries that calculates the total salary and the number of employees in each department.
  2. Use the CTE: We then use this CTE to calculate the average salary for each department.

Code Example

WITH department_salaries AS (
    SELECT department_id, SUM(salary) AS total_salary, COUNT(employee_id) AS num_employees
    FROM employees
    GROUP BY department_id
)
SELECT department_id, total_salary / num_employees AS average_salary
FROM department_salaries;

Explanation

  • CTE Definition: The WITH department_salaries AS clause defines a CTE that calculates the total salary and the number of employees for each department.
  • Main Query: The main query selects the department ID and calculates the average salary by dividing the total salary by the number of employees.

Recursive CTEs

Recursive CTEs are used to perform operations that require iterative processing, such as traversing hierarchical data structures.

Syntax

The syntax for a recursive CTE is as follows:

WITH RECURSIVE cte_name AS (
    -- Anchor member
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
    UNION ALL
    -- Recursive member
    SELECT column1, column2, ...
    FROM cte_name
    JOIN table_name ON cte_name.column = table_name.column
)
SELECT column1, column2, ...
FROM cte_name;

Practical Example

Let's consider an example where we want to find all employees in a hierarchical organization structure starting from a specific manager.

Code Example

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: Select the manager
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive member: Select employees reporting to the manager
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, manager_id, employee_name
FROM employee_hierarchy;

Explanation

  • Anchor Member: The initial query selects the top-level manager (where manager_id is NULL).
  • Recursive Member: The recursive query selects employees who report to the employees in the previous level of the hierarchy.
  • Main Query: The main query selects all employees in the hierarchy.

Exercises

Exercise 1: Basic CTE

Write a CTE to find the total sales for each product category in a sales table.

Solution

WITH category_sales AS (
    SELECT category_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY category_id
)
SELECT category_id, total_sales
FROM category_sales;

Exercise 2: Recursive CTE

Write a recursive CTE to find all subordinates of a specific manager in an employees table.

Solution

WITH RECURSIVE subordinates AS (
    -- Anchor member: Select the manager
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id = 1  -- Replace with the specific manager's ID
    UNION ALL
    -- Recursive member: Select employees reporting to the manager
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT employee_id, manager_id, employee_name
FROM subordinates;

Common Mistakes and Tips

  • Incorrect Anchor Member: Ensure the anchor member of a recursive CTE correctly initializes the recursion.
  • Infinite Recursion: Avoid infinite recursion by ensuring the recursive member eventually terminates.
  • CTE Naming: Use meaningful names for CTEs to improve query readability.

Conclusion

Common Table Expressions (CTEs) are a versatile tool in SQL that can simplify complex queries and make them more readable. By understanding both non-recursive and recursive CTEs, you can handle a wide range of data processing tasks efficiently. In the next module, we will explore Window Functions, which provide advanced analytical capabilities in SQL.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved