Common Table Expressions (CTEs)

Common Table Expressions, or CTEs, are one of the advanced features in PostgreSQL. They offer a more readable and organized way to write complex SQL queries. CTEs can be thought of as temporary views created just for one query. They can be especially useful when working with recursive queries, or when a subquery is needed multiple times in a larger query.

Syntax of CTEs

The basic syntax of a CTE is as follows:

WITH cte_name AS (
    -- subquery --
)
SELECT * FROM cte_name;

Here, the WITH keyword begins the CTE, followed by the name you want to assign to the CTE (cte_name in the example). The CTE name is then used in the main query to refer to the subquery defined in the CTE.

Example of a Simple CTE

Let's take a look at a simple example to illustrate how CTEs work.

Suppose you have a employees table and you want to get the employees who have a salary above the average salary. Without using a CTE, you might write a query like this:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

The same query can be re-written using a CTE as follows:

WITH average_salary AS (
    SELECT AVG(salary) AS avg FROM employees
)
SELECT name, salary
FROM employees, average_salary
WHERE salary > avg;

The CTE average_salary calculates the average salary from the employees table, and then the main query uses this average to filter the employees.

Recursive CTEs

One of the main uses of CTEs is for writing recursive queries. A recursive query is one that refers to itself.

Here's the syntax for a recursive CTE:

WITH RECURSIVE cte_name AS (
    -- non-recursive term --
    UNION ALL
    -- recursive term --
)
-- main query --

The non-recursive term is the part of the query that provides the base result set for the recursion. The recursive term is the part of the query that refers back to the CTE itself.

Example of a Recursive CTE

Here's an example of a recursive CTE. Suppose you have a employees table, where each row represents an employee and includes a column for the employee's manager. You could use a recursive CTE to find all employees reporting, directly or indirectly, to a particular manager.

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE name = 'John Doe' -- non-recursive term
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id -- recursive term
)
SELECT * FROM employee_hierarchy;

In this query, the non-recursive term selects the employee named 'John Doe'. Then, the recursive term joins the employees table with the CTE itself to find all employees whose manager is in the CTE, adding them to the CTE. The recursion continues until no more employees are found.

Conclusion

CTEs in PostgreSQL provide a powerful tool for creating readable and maintainable SQL queries. They allow you to break down complex queries into simpler parts and can make recursive queries much easier to write and understand. Whether you're just starting out with PostgreSQL or you're an experienced user, understanding how to use CTEs can be a great addition to your SQL toolkit.