CTE (Common Table Expression) is a feature in SQL that allows you to define a temporary result set that can be used within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE is similar to a derived table or subquery, but has some advantages over those techniques, including improved readability, reusability, and optimization opportunities.
A CTE is defined using the WITH clause, followed by a SELECT statement. The result set of the SELECT statement is then referred to as the CTE and can be used within the main query.
Here's an example of using a CTE to simplify a query:
WITH cte AS ( SELECT name, SUM(amount) AS total_amount FROM sales GROUP BY name ) SELECT name, total_amount FROM cte WHERE total_amount > 1000;
In this example, the CTE is defined as the result of a SELECT statement that aggregates sales data by name and calculates the total amount for each name. The main query then uses the CTE to retrieve only the names and amounts where the total amount is greater than 1000.
Here's an example of using a CTE in an UPDATE statement:
WITH cte AS ( SELECT id, salary, salary * 1.1 AS new_salary FROM employees WHERE department = 'sales' ) UPDATE cte SET salary = new_salary;
In this example, the CTE is defined as the result of a SELECT statement that retrieves the ID, salary, and a calculated new salary for all employees in the sales department. The main query then updates the salary for those employees to the new salary.
Note that the CTE is only used to simplify the calculation and selection of the data to be updated, and is not a permanent object in the database. Once the UPDATE statement is executed, the CTE is discarded.