Common Table Expressions (CTE)

Introduction to CTE

Common Table Expressions (CTE) in SQL provide a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve readability and maintainability, making complex queries easier to manage.

Why Use CTEs?

• Improves Query Readability: Organizes complex queries into logical sections.
• Enhances Code Reusability: A CTE can be referenced multiple times within a query.
• Self-Referencing (Recursive CTEs): Enables hierarchical queries like organizational structures.
• Simplifies Queries: Reduces the need for nested subqueries.

Syntax of CTE

A CTE is defined using the WITH keyword, followed by the CTE name and the query.

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

Example of a Simple CTE

Let’s consider an employees table with the following columns: id, name, department, and salary.

WITH HighSalaryEmployees AS (
    SELECT name, department, salary
    FROM employees
    WHERE salary > 50000
)
SELECT * FROM HighSalaryEmployees;

This query selects employees earning more than 50,000 and stores them in a temporary result set called HighSalaryEmployees.

CTE vs Subquery

When to Use CTEs

• When dealing with complex queries requiring multiple references to the same subquery.
• When working with hierarchical or recursive data.
• When breaking down long queries into manageable sections.

Conclusion

CTEs offer a powerful way to simplify and optimize SQL queries. By making code more readable and reusable, they are an essential tool for SQL developers dealing with complex data operations. Understanding both simple and recursive CTEs will help in handling advanced query requirements effectively.