SQL CTE: рекурсивные общие табличные выражения в SQL
SQL CTE (Common Table Expressions)
Common Table Expressions (CTE) are temporary or named subqueries that can be used in other queries. They provide a convenient way to organize data hierarchies, perform recursive queries, and simplify complex queries.
CTEs are used to create a temporary table that remains available only for the duration of the query execution and is then deleted. The example below shows how to create a CTE:
<pre><code class="sql">WITH cte_name (column_list) AS
(
SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;
</code>
Let's consider a simple example where we create a CTE to calculate the total salary of all employees:
<pre><code class="sql">WITH employee_cte (employee_name, salary) AS
(
SELECT name, salary
FROM employees
)
SELECT SUM(salary) as total_salary
FROM employee_cte;
</code>
In this example, we create a CTE named "employee_cte" that retrieves names and salaries from the "employees" table. Then we execute the main query using the CTE and obtain the total salary of all employees.
CTEs can also be used to perform recursive queries, which are queries that refer to themselves. Let's consider an example where we create an employee hierarchy in a company:
<pre><code class="sql">WITH RECURSIVE employee_hierarchy (employee_id, manager_id, employee_name) AS
(
SELECT employee_id, manager_id, employee_name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name
FROM employees e
INNER JOIN employee_hierarchy eh ON eh.employee_id = e.manager_id
)
SELECT employee_id, manager_id, employee_name
FROM employee_hierarchy;
</code>
In this example, we create a CTE named "employee_hierarchy" that retrieves all employees whose "manager_id" value is NULL (i.e., managers). Then we use the UNION ALL operator to combine the results from the "employees" table and the "employee_hierarchy" CTE, using recursive linking.
CTEs provide a convenient way to organize and structure complex queries, improving code readability and maintainability. They also allow you to avoid the need for temporary tables and reuse code.
Here are some of the key advantages of using CTE:
- Simplification of complex queries
- Improved performance
- Readability and maintainability
- Usage of recursive queries
- Data security
- Platform and DBMS independence
CTE is a powerful tool in SQL that allows you to work with data more efficiently and simplify query development. Whether you use CTE to organize data hierarchies or perform complex analytical queries, it is a useful tool worth learning and using in your SQL queries.