What is CTE purpose?

A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The purpose of a CTE includes:

1. Improved Readability: CTEs can make complex queries easier to read and understand by breaking them into simpler, named components.

2. Recursion: CTEs support recursive queries, allowing you to work with hierarchical data structures, such as organizational charts or tree structures.

3. Modularity: You can define a CTE once and reference it multiple times within the same query, which can reduce redundancy and improve maintainability.

4. Simplifying Joins and Subqueries: CTEs can simplify complex joins and subqueries, making it easier to manage and modify queries.

Example

Here’s a simple example of a CTE:

WITH EmployeeCTE AS (
    SELECT employee_id, employee_name, department_id
    FROM employees
    WHERE status = 'active'
)
SELECT e.employee_id, e.employee_name, d.department_name
FROM EmployeeCTE e
JOIN departments d ON e.department_id = d.department_id;

In this example, the CTE EmployeeCTE retrieves active employees, which is then used in the main query to join with the departments table.