What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary, named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. By breaking complex queries into smaller, modular components, CTEs greatly improve readability and maintainability. When working with large datasets and multiple tables, CTEs can also enhance performance by organizing queries more efficiently. This improved structure can reduce the overall execution time of complex queries.

Syntax

The basic syntax for defining a CTE is as follows:

SQL

// This part defines a temporary table (CTE) called 'cte_name'

WITH cte_name AS (
    // Selects column1 and column2 from the specified table where a certain condition is met
    SELECT column1, column2
    FROM table_name
    WHERE condition  // Filters the rows based on this condition
)

// This part uses the temporary table created above to select the columns

SELECT column1, column2
FROM cte_name
WHERE another_condition; // Applies another condition to further filter the rows from the CTE

Main Uses of CTEs

1. Reusability: CTEs can be referenced multiple times within the same query, allowing for code reuse without redundancy.
2. Improving Readability: By naming the CTEs, you can make the intent of the query clearer, which is beneficial for collaboration and future maintenance.
3. Simplifying Complex Queries: CTEs help deconstruct complicated SQL statements into simpler parts, making it easier to understand and maintain the code.
4. Recursive Queries: CTEs can be used to perform recursive queries, which are useful for hierarchical data retrieval, such as organizational structures or category trees.

Example of a CTE

Consider a scenario where you have an employees table with the following data:

To calculate the average salary for each department, you can use a CTE as follows:

SQL

// This part creates a temporary table (CTE) named 'dept_salaries'

WITH dept_salaries AS ( 
    SELECT department_id,  
           SUM(salary) AS total_salary,  // Calculates the total salary for each department
           COUNT(*) AS employee_count    // Counts the number of employees in each department
    FROM employees
    GROUP BY department_id            // Groups the data by each department
)

// This part uses the temporary table created above to calculate the average salary per department

SELECT department_id, 
       total_salary / employee_count AS average_salary // Divides the total salary by the number of employees to get the average salary
FROM dept_salaries;

Resulting Table:

Example of Multiple CTEs

In this example, we’ll demonstrate how to use multiple Common Table Expressions (CTEs) to analyze a company’s sales data, expenses, and net profit over several years. We will work with three tables: sales, expenses, and revenues.

SQL

// This part creates a temporary table (CTE) called 'yearly_sales' to calculate total units sold and revenue per year

WITH yearly_sales AS (
    SELECT 
        year,  // Selects the year from the 'sales' table
        SUM(units_sold) AS total_units_sold,  // Adds up all units sold for each year
        SUM(revenue) AS total_revenue  // Adds up the revenue generated for each year
    FROM 
        sales  // The 'sales' table is the source of the data
    GROUP BY 
        year  // Groups the data by each year so calculations are done per year
),

// This part creates another temporary table (CTE) called 'yearly_expenses' to calculate total expenses per year

yearly_expenses AS (
    SELECT 
        year,  // Selects the year from the 'expenses' table
        SUM(amount) AS total_expenses  // Adds up all expenses for each year
    FROM 
        expenses  // The 'expenses' table is the source of the data
    GROUP BY 

        year  // Groups the data by each year so calculations are done per year
),

// This part creates a CTE called 'net_profit_calculation' to join the two previous CTEs and calculate net profit per year

net_profit_calculation AS (
    SELECT 
        s.year,  // Selects the year from the 'yearly_sales' CTE
        s.total_units_sold,  // Brings in the total units sold from the 'yearly_sales' CTE
        s.total_revenue,  // Brings in the total revenue from the 'yearly_sales' CTE
        e.total_expenses,  // Brings in the total expenses from the 'yearly_expenses' CTE
        (s.total_revenue - e.total_expenses) AS net_profit  // Subtracts expenses from revenue to calculate net profit for each year
    FROM 
        yearly_sales s  // References the 'yearly_sales' CTE
    JOIN 
        yearly_expenses e ON s.year = e.year  // Joins 'yearly_sales' and 'yearly_expenses' on the year column
)

// This final part selects the data from the 'net_profit_calculation' CTE to display results

SELECT 
    npc.year,  // Selects the year
    npc.total_units_sold,  // Displays the total units sold for the year
    npc.total_revenue,  // Displays the total revenue for the year
    npc.total_expenses,  // Displays the total expenses for the year
    npc.net_profit  // Displays the net profit (revenue minus expenses) for the year
FROM 
    net_profit_calculation npc  // References the 'net_profit_calculation' CTE
ORDER BY 
    npc.year;  // Orders the results by year in ascending order

Resulting Table for Multiple CTEs:

• CTE yearly_sales: This CTE calculates the total units sold and total revenue for each year from the sales table.
• CTE yearly_expenses: This CTE calculates the total expenses for each year from the expenses table.
• CTE net_profit_calculation: This CTE joins the yearly_sales and yearly_expenses results to calculate the net profit for each year by subtracting total expenses from total revenue.
• Final SELECT Query: The final query selects all fields from the net_profit_calculation CTE, providing a comprehensive overview of sales, expenses, and profit by year.

Conclusion

Common Table Expressions are indispensable tools in SQL for simplifying and organizing complex queries. By using CTEs, you can create more efficient, readable, and maintainable SQL queries that provide deeper insights into your data. Mastering CTEs allows for better data management and manipulation, ultimately enhancing your ability to analyze and report on data effectively. Additionally, leveraging multiple CTEs in a single query can help tackle complex data analysis tasks with ease.

Happy Querying !!!