Working with Common Table Expressions (CTEs) in SQL

CTE (Common Table Expression) is a tool in SQL for splitting complex queries, improving readability and maintenance. It is a temporary result set that can be referenced multiple times in a single query, with a clear structure, especially suitable for nested subqueries and recursive data processing. The basic syntax is WITH cte_name AS (query statement) SELECT * FROM cte_name; for example, when counting the number of employees in the department, you can first define the dept_count CTE and then filter the results. Using CTE can disassemble logic in steps, such as first calculating the average salary of each department, and then finding employees who are above the average salary, making the code easier to understand. CTE also supports recursive queries, suitable for tree structure data, in the format WITH RECURSIVE cte_name AS (UNION ALL recursive query), such as finding an employee and all subordinates. Notes include: the recursive part must reference itself and have termination conditions; different database support levels are different; CTE is not materialized, and repeated references may affect performance; avoid conflicts with table names; and attention should be paid to database version in terms of compatibility. It is recommended to give priority to using CTE when you need to disassemble logic, recursive queries, or improve readability.

CTE (Common Table Expression) is a very practical tool in SQL that allows you to split complex queries into more readable and maintainable parts. Especially when dealing with nested subqueries or recursive data, the advantages of CTE's clear structure are very obvious.

What is CTE?

CTE is a temporary result set that can be referenced multiple times in a query. It is not a physical table, but a logical "temporary view", and its scope is limited to the query that defines it.

The basic syntax is as follows:

 WITH cte_name AS (
    -- Query statement)
SELECT * FROM cte_name;

For example, if you want to count the number of employees in each department, you can write it like this:

 WITH dept_count AS (
    SELECT department_id, COUNT(*) AS num_employees
    FROM employees
    GROUP BY department_id
)
SELECT * FROM dept_count WHERE num_employees > 5;

This not only has a clear structure, but also facilitates debugging and reuse.

Improve code readability using CTE

Many people like to use nested subqueries to solve problems at the beginning, but once there are more levels, SQL becomes difficult to understand and maintain. And using CTE can be written layer by layer, and each part is clear and clear.

For example: Suppose you want to find employees whose salary is higher than the average salary in your department. If you write with a subquery, you may have nested three layers, which looks very confusing; but if you use CTE:

 WITH avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
),
high_earners AS (
    SELECT e.*
    FROM employees e
    JOIN avg_salary a ON e.department_id = a.department_id
    WHERE e.salary > a.avg_sal
)
SELECT * FROM high_earners;

Write it in steps and others will know what you are doing at a glance.

Recursive CTE

One of the most powerful functions of CTE is that it supports recursive queries, which are suitable for tree structures or hierarchical data, such as organizational structures, directory structures, etc.

The basic format is as follows:

 WITH RECURSIVE cte_name AS (
    -- Initial query (anchor member)
    SELECT ...
    UNION ALL
    -- Recursive query SELECT...
)
SELECT * FROM cte_name;

For example, find an employee and all of his subordinates:

 WITH RECURSIVE subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE employee_id = 100 -- Starting employee UNION ALL

    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

A few points to note:

• The recursive part must refer to the CTE itself
• There must be a termination condition, otherwise there will be infinite loops
• Different databases have slightly different support for recursive CTE. Check the document before using it.

Some precautions in practical applications

Although CTE is very useful, there are some details that need to be paid attention to:

• Performance issues : CTE is not a materialized view, and it will be executed again every time the reference is referenced. If you use multiple CTEs on a large table, and each is called multiple times, it may slow down the query.
• Naming conflict : Do not give the CTE the same name as the actual table, as it is easy to be confused.
• Compatibility : Although mainstream databases support CTE (such as PostgreSQL, MySQL 8.0, and SQL Server), some older versions may not support recursive CTE.

It is recommended to prioritize CTE in the following scenarios:

• Query is complicated and requires disassembly of logic
• Recursive query is required
• Want to improve code readability and maintenance

Basically that's it. CTE is a very practical way in SQL writing. After mastering it, you will find that many complex queries can become clearer and easier to understand.

The above is the detailed content of Working with Common Table Expressions (CTEs) in SQL. For more information, please follow other related articles on the PHP Chinese website!