PostgreSQL - Sub Queries

PostgreSQL - Subqueries and CTEs

A subquery, also known as an inner or nested query, is a SELECT query that is embedded inside another main query (e.g., in a WHERE, FROM, or SELECT clause). Its purpose is to return a result set that the main query can use to filter, evaluate, or retrieve further data.

Subqueries are a fundamental part of SQL, but for complex logic, modern PostgreSQL offers a more readable and powerful alternative: Common Table Expressions (CTEs).

Subqueries with the WHERE Clause

This is the most common use case. The subquery returns a value or a list of values used for comparison.

Syntax Guidelines

• Subqueries must be enclosed in parentheses ().
• A subquery used with a comparison operator (=, >, <) should return a single value (a scalar subquery).
• A subquery used with IN or NOT IN can return a list of multiple values.
• A subquery used with EXISTS checks for the existence of any rows in its result set.

Example

Consider two tables, employees and departments.

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2),
    department_id INT REFERENCES departments(id)
);

INSERT INTO departments (name) VALUES ('Sales'), ('Engineering'), ('HR');
INSERT INTO employees (name, salary, department_id) VALUES
('Alice', 90000, 2), ('Bob', 85000, 2), ('Charlie', 70000, 1), ('David', 45000, 3);

Let’s find all employees who work in the ‘Engineering’ department using a subquery.

SELECT name, salary
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Engineering');

This would produce the following result:

  name  | salary
--------+---------
 Alice  | 90000.00
 Bob    | 85000.00
(2 rows)

Best Practice: While the subquery above works, a JOIN is often more readable and can be more performant: SELECT e.name, e.salary FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering';

Improving Readability with Common Table Expressions (CTEs)

When queries become complex with multiple nested subqueries, they can be difficult to read and debug. Common Table Expressions (CTEs), defined using the WITH clause, allow you to create temporary, named result sets that you can reference within your main query. They act like temporary tables for a single query.

Syntax

WITH cte_name AS (
    -- Subquery defining the CTE
    SELECT ...
)
-- Main query that uses the CTE
SELECT ... FROM cte_name;

Example: Rewriting a complex query with a CTE

Let’s find the departments where the average salary is above the overall average salary for the entire company.

Without a CTE, this can be messy:

SELECT name from departments WHERE id IN (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
);

Using a CTE makes the logic much clearer:

WITH department_avg_salaries AS (
    SELECT department_id, AVG(salary) as avg_dept_salary
    FROM employees
    GROUP BY department_id
),
overall_avg_salary AS (
    SELECT AVG(salary) as avg_overall_salary FROM employees
)
SELECT d.name
FROM departments d
JOIN department_avg_salaries das ON d.id = das.department_id
WHERE das.avg_dept_salary > (SELECT avg_overall_salary FROM overall_avg_salary);

This CTE-based query is more verbose but also more structured, self-documenting, and easier to maintain.

Subqueries with INSERT, UPDATE, and DELETE

Subqueries are also useful for data modification statements.

INSERT with Subquery

You can insert the results of a query into another table. For example, archiving high-earning employees.

CREATE TABLE high_earners_archive (name VARCHAR(100), salary NUMERIC);

INSERT INTO high_earners_archive (name, salary)
SELECT name, salary
FROM employees
WHERE salary > 80000;

UPDATE with Subquery

Give a 10% raise to all employees in the ‘Sales’ department.

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

DELETE with Subquery

Remove all employees from the ‘HR’ department.

DELETE FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');