Subqueries and EXISTS

A subquery is a query inside another query. It can produce a single value, a list of values, or a whole table.

Example 1: scalar subquery

Employees paid more than the company average.

SELECT name, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees)
ORDER BY salary DESC;

 name  | salary 
-------+--------
 Meera | 250000
 Ravi  | 180000
 Divya | 160000
 Kiran | 150000
 Neha  | 150000
(5 rows)

If a scalar subquery returns more than one row, the query fails.

SELECT name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 1);

ERROR:  more than one row returned by a subquery used as an expression

Example 2: IN with a list subquery

Customers who have at least one delivered order.

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'delivered')
ORDER BY name;

 name  
-------
 Aarav
 Isha
 Karan
(3 rows)

Example 3: subquery in FROM

Average salary per department, then compare each employee to it in the outer query.

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (SELECT dept_id, round(avg(salary)) AS avg_salary
      FROM employees
      GROUP BY dept_id) d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary
ORDER BY e.id;

  name  | salary | avg_salary 
--------+--------+------------
 Meera  | 250000 |     168000
 Ravi   | 180000 |     168000
 Divya  | 160000 |     111667
 Vikram | 120000 |     100000
(4 rows)

Example 4: correlated subquery

A correlated subquery refers to the outer row, so it is evaluated once per outer row (conceptually). Employees paid above their own department's average:

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT avg(salary)
                  FROM employees x
                  WHERE x.dept_id = e.dept_id)
ORDER BY e.id;

  name  | salary 
--------+--------
 Meera  | 250000
 Ravi   | 180000
 Divya  | 160000
 Vikram | 120000
(4 rows)

Example 5: subquery in SELECT

SELECT c.name,
       (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY c.id;

  name  | orders 
--------+--------
 Aarav  |      3
 Isha   |      2
 Karan  |      2
 Lata   |      1
 Manish |      1
 Nisha  |      0
(6 rows)

A join with GROUP BY usually does the same work with one pass instead of one lookup per row.

Example 6: EXISTS

EXISTS is true when the subquery returns at least one row. What the subquery selects does not matter; SELECT 1 is the convention.

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id AND o.status = 'cancelled');

 name 
------
 Isha
(1 row)

Example 7: NOT EXISTS

Products that were never ordered.

SELECT p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

 name 
------
 Desk
(1 row)

Example 8: EXISTS vs IN

Both give the same rows when the subquery column cannot be NULL. They differ in two ways:

• EXISTS stops at the first match and handles NULL correctly.
• NOT IN returns nothing if the subquery yields a single NULL (see NULL handling).

Modern optimizers often plan IN and EXISTS the same way. Choose EXISTS/NOT EXISTS for correctness, and IN for readability with a fixed list.

Example 9: ANY and ALL

> ALL means greater than every value in the list. > ANY means greater than at least one.

SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2)
ORDER BY salary;

 name  | salary 
-------+--------
 Ravi  | 180000
 Meera | 250000
(2 rows)

SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 3)
  AND dept_id = 4;

 name  | salary 
-------+--------
 Rohan |  95000
(1 row)

Example 10: highest salary per department (with a subquery)

SELECT e.dept_id, e.name, e.salary
FROM employees e
WHERE e.salary = (SELECT max(salary) FROM employees x WHERE x.dept_id = e.dept_id)
ORDER BY e.dept_id;

 dept_id |  name  | salary 
---------+--------+--------
       1 | Meera  | 250000
       2 | Divya  | 160000
       3 | Vikram | 120000
       4 | Rohan  |  95000
(4 rows)

Ties return every tied row. Window functions solve the same problem with more control.

Example 11: second highest salary

SELECT max(salary) AS second_highest
FROM employees
WHERE salary < (SELECT max(salary) FROM employees);

 second_highest 
----------------
         180000
(1 row)

Example 12: rows compared as a pair

SELECT name, dept_id, salary
FROM employees
WHERE (dept_id, salary) IN (SELECT dept_id, max(salary) FROM employees GROUP BY dept_id)
ORDER BY dept_id;

  name  | dept_id | salary 
--------+---------+--------
 Meera  |       1 | 250000
 Divya  |       2 | 160000
 Vikram |       3 | 120000
 Rohan  |       4 |  95000
(4 rows)

Summary

• A scalar subquery must return one row or the query fails.
• A correlated subquery reads the outer row.
• Use NOT EXISTS, not NOT IN, to find rows without a match.