PostgreSQL - Subquery - GeeksforGeeks

PostgreSQL - Subquery

Last Updated : 5 Nov, 2026

A subquery in PostgreSQL is a query nested inside another SQL query. It is used to perform complex filtering, comparisons and calculations by using the result of one query within another.

  • Apply aggregate functions such as SUM(), COUNT(), AVG(), MIN() and MAX().
  • Update or delete records using data from another table..

PostgreSQL Clauses That Support Subqueries

Subqueries can be used with the following clauses:

  • SELECT: Returns calculated values or additional information using a subquery.
  • FROM: Treats the subquery as a temporary table (derived table).
  • WHERE: Filters rows based on the result returned by the subquery.
  • HAVING: Filters grouped data using the result of a subquery.
  • INSERT: Inserts data returned by a subquery into another table.
  • UPDATE: Updates rows using values returned by a subquery.
  • DELETE: Deletes rows based on the result returned by a subquery.

Types of PostgreSQL Subqueries

Consider the following tables.

employees Table:

Screenshot-2026-11-04-155649

department Table:

Screenshot-2026-11-04-155729

Single Row Query

A single-row subquery returns only one value.

  • Returns exactly one row.
  • Used with comparison operators such as =, >, <, >= and <=.

Example:

SELECT *
FROM employees
WHERE salary =
(
    SELECT MAX(salary)
    FROM employees
);

Output:

Screenshot-2026-11-04-155906
  • The subquery finds the maximum salary.
  • The outer query returns the employee with that salary.

Multi-Row Subquery

A multi-row subquery returns multiple rows.

  • Returns more than one row.
  • Used with operators such as IN, ANY and ALL.

Example:

SELECT employee_name, department
FROM employees
WHERE department IN
(
    SELECT department_name
    FROM departments
    WHERE location = 'New York'
);

Output:

Screenshot-2026-11-04-160137
  • The subquery finds departments located in New York.
  • The outer query returns employees working in those departments.

Correlated Subquery

A correlated subquery depends on the outer query.

Features:

  • References columns from the outer query.
  • Executes once for every row processed by the outer query.
  • Useful for row-by-row comparisons.

Example:

SELECT e.employee_name,
       e.department,
       e.salary
FROM employees e
WHERE e.salary >
(
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

Output:

Screenshot-2026-11-04-160029
  • The subquery calculates the average salary for each department.
  • The outer query returns employees earning more than their department's average salary.

Working

Example: Subquery in WHERE Clause

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

Output:

Screenshot-2026-11-04-160137
  • Finds employees earning above the average salary.

Example: Subquery with UPDATE

UPDATE employees
SET salary = salary + 5000
WHERE department IN
(
    SELECT department_name
    FROM departments
    WHERE location = 'New York'
);

Output:

Screenshot-2026-11-04-160247
  • Increases the salary of employees working in departments located in New York.

Example: Subquery with DELETE

DELETE FROM employees
WHERE department IN
(
    SELECT department_name
    FROM departments
    WHERE location = 'Chicago'
);

Output:

Screenshot-2026-11-04-160344
  • Deletes employees working in departments located in Chicago.

Example: Subquery in FROM Clause

SELECT AVG(salary) AS average_salary
FROM
(
    SELECT salary
    FROM employees
    WHERE salary > 50000
) AS high_salary_employees;

Output:

Screenshot-2026-11-04-160419
  • The subquery creates a temporary table containing salaries greater than 50,000.
  • The outer query calculates the average salary from that temporary table.
Comment

Explore