PostgreSQL subqueries

A subquery is a SELECT inside another statement, in parentheses. Where it appears decides what it must return:

Scalar subquery: one row, one column, used like a single value (price > (SELECT avg(price) FROM products)).
IN / NOT IN: a one-column list. Careful: NOT IN returns nothing if the list contains a NULL. Prefer NOT EXISTS.
EXISTS / NOT EXISTS: true if the subquery returns at least one row. Usually correlated, meaning it refers to the outer row.
Derived table: a subquery in FROM, which must have an alias.
ANY / ALL: compare a value with every value the subquery returns.
LATERAL: a subquery in FROM that can see columns of earlier tables, perfect for “top N per row”.

Syntax

SELECT ... FROM t WHERE col > (SELECT ...);
SELECT ... FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.x = t.x);
SELECT ... FROM (SELECT ...) AS sub;

Examples

Edit the code and run it. Output is printed the way psql prints it.

8.1Scalar subquery

SELECT name, price,
       round(price - (SELECT avg(price) FROM products), 2) AS diff_from_avg
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

The inner query computes one number (the average price). The outer query uses it twice: to filter and to compute a difference.

8.2IN with a subquery

1
2
3

SELECT name, country
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'shipped');

The subquery returns a list of customer ids; the outer query keeps customers in that list. Duplicates in the list don’t matter.

8.3Correlated EXISTS

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON p.id = oi.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Audio'
);

For each customer c, the inner query looks for at least one Audio item they bought. SELECT 1 is a convention: EXISTS only cares whether a row exists, not what it contains.

8.4Derived table in FROM

SELECT round(avg(order_total), 2) AS avg_order_value,
       max(order_total)              AS biggest_order
FROM (
  SELECT order_id, sum(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
) AS totals;

First compute a total per order, then aggregate those totals. You can’t nest avg(sum(...)) directly, so a derived table (or a CTE) is the tool.

8.5ALL and LATERAL

-- employees paid more than everyone in Sales
SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);

-- the 2 most recent orders of every customer
SELECT c.name, recent.id AS order_id, recent.order_date
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.order_date
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.order_date DESC
  LIMIT 2
) AS recent
ORDER BY c.name, recent.order_date DESC;

> ALL must beat every value in the list. LATERAL lets the subquery use c.id from the outer row, so it runs once per customer and the LIMIT 2 applies per customer.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 8.1

Show the name and price of products priced above the average product price.

Hint

WHERE price > (SELECT avg(price) ...)

Solution

SELECT name, price FROM products WHERE price > (SELECT avg(price) FROM products);

Exercise 8.2

Using NOT EXISTS, list the first_name of employees who are nobody’s manager.

Hint

NOT EXISTS (SELECT 1 FROM employees x WHERE x.manager_id = e.id)

Solution

SELECT e.first_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees x WHERE x.manager_id = e.id);

Exercise 8.3

Show customers (name) whose total spend across all non-cancelled orders is greater than 1000, along with spend.

Hint

Derived table with sum per customer, then join or filter.

Solution

SELECT c.name, t.spend FROM customers c JOIN (SELECT o.customer_id, sum(oi.quantity * oi.unit_price) AS spend FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status <> 'cancelled' GROUP BY o.customer_id) t ON t.customer_id = c.id WHERE t.spend > 1000;