Use Common Table Expressions (CTEs) for Complex Queries

Common Table Expressions (CTEs) are an SQL feature that allows you to create temporary result sets within your query. They can make your SQL code more readable and maintainable, especially when dealing with complex queries. Here's how you can use CTEs:

• Define a CTE: To create a CTE, use the WITH keyword followed by a subquery enclosed in parentheses. Give the subquery a name and specify the columns it returns.

   WITH cte_name (column1, column2, ...) AS (
       -- Subquery here
   )

• Reference the CTE: After defining the CTE, you can reference it in the main query as if it were a regular table.

   SELECT *
   FROM cte_name;

Here are some benefits and use cases for CTEs:

• Readability: CTEs make your SQL code more readable by breaking down complex logic into smaller, named, and self-contained units.

• Code Reuse: You can reuse CTEs within the same query or in multiple queries, reducing redundancy and promoting consistency.

• Simplifying Subqueries: CTEs are particularly useful for simplifying subqueries and recursive queries, making them easier to understand.

Example: Suppose you want to find the employees who have been with the company for more than five years and earn more than a certain threshold. You can use a CTE to calculate their years of service and then filter the results:

WITH employee_years_of_service AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        hire_date,
        EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) AS years_of_service
    FROM employees
)
SELECT
    first_name,
    last_name,
    years_of_service
FROM employee_years_of_service
WHERE years_of_service > 5
AND salary > 50000;

By using a CTE, you break down the logic into two distinct parts: calculating years of service and then filtering based on that calculation. This makes the query easier to understand and maintain.

CTEs are a valuable tool for SQL developers, and they can help you write more efficient and maintainable SQL queries, especially when dealing with complex data manipulations or multiple levels of subqueries.