Scalar, list, correlated and derived-table subqueries

Scalar subquery: one value

Products priced above the average price:

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

            name             |  price
-----------------------------+----------
 27-inch monitor             | 18999.00
 Office chair                | 14500.00
 Noise-cancelling headphones | 12999.00
 Portable SSD 1TB            |  7999.00

The inner query runs and its single value takes its place in the comparison. A subquery used as a value must return exactly one row and one column. If it returns more, PostgreSQL stops:

SELECT name, price FROM products
WHERE price > (SELECT price FROM products WHERE category = 'Furniture');

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

There are two furniture products, so “the furniture price” does not exist. If a scalar subquery returns no rows, there is no error: its value is NULL.

List subquery: IN

After IN, a subquery can return many rows of one column. Orders that contain a furniture product:

SELECT o.id FROM orders o
WHERE o.id IN (
  SELECT oi.order_id
  FROM order_items oi JOIN products p ON p.id = oi.product_id
  WHERE p.category = 'Furniture'
)
ORDER BY o.id;

Orders 105 and 112.

Correlated subquery: once per row

A subquery can refer to the row the outer query is looking at. Then it is correlated, and its result differs for each outer row. Units sold of each product:

SELECT p.name,
       (SELECT coalesce(sum(oi.quantity), 0)
        FROM order_items oi
        WHERE oi.product_id = p.id) AS units_sold
FROM products p
ORDER BY units_sold DESC, p.name
LIMIT 4;

      name       | units_sold
-----------------+------------
 Wireless mouse  |          5
 27-inch monitor |          3
 Laptop stand    |          3
 Desk lamp       |          2

oi.product_id = p.id links the inner query to the current product. EXISTS subqueries from Joins are correlated too. Logically the subquery runs once per outer row; in practice PostgreSQL often finds a cheaper plan, which the query planning topics show how to inspect.

Derived table: a query in FROM

A subquery in FROM acts as a temporary table for the rest of the query. It must be in parentheses and needs an alias. Average order value across non-cancelled orders, computed in two steps:

SELECT round(avg(order_total), 2) AS avg_order_value, count(*) AS orders
FROM (
  SELECT oi.order_id, sum(oi.quantity * oi.unit_price) AS order_total
  FROM order_items oi JOIN orders o ON o.id = oi.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY oi.order_id
) AS totals;

 avg_order_value | orders
-----------------+--------
        13089.64 |     11

The inner query turns items into one total per order; the outer query averages those totals. This “aggregate, then aggregate again” shape is impossible in a single GROUP BY, and it is the main reason derived tables exist. Common table expressions later give the same idea names and a cleaner layout.

Check yourself

1. What must a subquery used in WHERE price > ( … ) return?

   Show answer

   Exactly one row with one column. More than one row is an error; no rows makes the value NULL.

   I could explain this without looking

2. What makes a subquery correlated?

   Show answer

   It refers to a column of the outer query, such as oi.product_id = p.id, so its result depends on the current outer row.

   I could explain this without looking

3. Why do you need a derived table to compute the average order value from order_items?

   Show answer

   The order totals are themselves sums over items. You must first group items into one total per order, then average those totals: two levels of aggregation, which needs a query inside a query.

   I could explain this without looking