PostgreSQL - WITH Clause

The WITH clause in PostgreSQL, also known as a Common Table Expression (CTE), simplifies complex queries by breaking them into smaller, readable sections. It allows us to define temporary result sets that can be referenced later in our main query. This makes the PostgreSQL code easier to manage and debug

This not only enhances query readability but also optimizes performance, especially for large queries involving multiple subqueries. In this article, we will explore the PostgreSQL WITH clause with detailed examples and outputs, showing how it can be used to improve query management and efficiency.

PostgreSQL - WITH Clause

WITH clause in PostgreSQL is primarily used to organize large queries and avoid repeated subqueries by defining temporary result sets. These result sets (referred to as CTEs) can be reused multiple times in the main query, making the SQL code easier to manage, debug, and maintain data integrity.

Syntax:

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
SELECT * 
FROM cte_name;

Parametes:

• cte_name: Name assigned to the temporary result set.
  
• SELECT statement: The query that defines the result set for the CTE.
  
• conditions: Any filtering or conditions applied within the CTE.
  
• SELECT FROM cte_name: Refers to the defined CTE in the main query.

Examples of PostgreSQL - WITH Clause

The WITH clause allows us to define temporary result sets (Common Table Expressions or CTEs) that can be reused in a query. This helps break down complex queries into manageable parts and improves readability and maintainability.

Employees Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'HR', 50000),
('Bob', 'IT', 60000),
('Charlie', 'HR', 55000),
('David', 'IT', 65000),
('Eva', 'Finance', 70000);

Example 1: Simple WITH Clause Usage

The main query then retrieves the result set from the CTE, displaying the department names along with their respective average salaries. This simplifies complex queries into a more readable structure. Find the average salary by department using a CTE.

Query:

WITH avg_salaries AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * 
FROM avg_salaries;

Output:

Explanation:

The CTE avg_salaries calculates the average salary for each department. The main query retrieves this data from the CTE, providing the result set.

Example 2: Using CTE with Filtering

In this example, the CTE avg_salaries calculates the average salary for each department. The main query then filters the result to display only the departments where the average salary exceeds 60,000. This showcases how CTEs can be combined with WHERE clauses to filter data effectively.

Query:

WITH avg_salaries AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * 
FROM avg_salaries
WHERE avg_salary > 60000;

Output:

Explanation:

This query filters out the departments with an average salary greater than 60000, showing only IT and Finance departments in the result.

Example 3: Multiple CTEs

In this example, two CTEs are defined total_employees calculates the number of employees per department, and avg_salaries calculates the average salary for each department. Calculate the total number of employees and the average salary per department using two CTEs.

Query:

WITH total_employees AS (
    SELECT department, COUNT(*) AS num_employees
    FROM employees
    GROUP BY department
),
avg_salaries AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT t.department, t.num_employees, a.avg_salary
FROM total_employees t
JOIN avg_salaries a ON t.department = a.department;

Output:

Explanation:

This query combines two CTEs one for counting employees and another for calculating average salaries and joins them to display the number of employees and average salary by department.

Conclusion

The PostgreSQL WITH clause is a versatile feature that enhances query readability and efficiency by allowing you to define reusable temporary result sets (CTEs). It helps in breaking down complex queries into smaller parts, making the SQL easier to manage and more efficient, especially for large queries.