Subqueries

A subquery is a query inside another query. Need items priced above the average? You need to calculate the average first, then filter by it. Subqueries let you do this in a single statement.

Three types of subqueries

Not necessarily. PostgreSQL is smart about optimizing subqueries. A scalar or IN subquery usually runs just once. Correlated subqueries (which reference the outer query) can run per-row, but the optimizer often rewrites them as JOINs internally.

scalar_subquery.sql
sql
-- Scalar subquery: items priced above average
SELECT
  product_id,
  price
FROM order_items
WHERE price > (SELECT AVG(price) FROM order_items)
ORDER BY price DESC
LIMIT 10;

-- The inner query returns a single number (the average)
-- The outer query uses it as a filter value

A scalar subquery returns exactly one value. It runs first, then the outer query uses that value.

While scalar subqueries return a single value, an IN subquery returns a list. This is perfect for filtering rows based on a set of values from another table.

in_subquery.sql
sql
-- IN subquery: customers who canceled an order
SELECT DISTINCT
  c.customer_id,
  c.customer_city,
  c.customer_state
FROM customers c
WHERE c.customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_status = 'canceled'
);

IN subquery returns a list of values. The outer query checks if each row matches any value in that list.

Use subqueries when you need a single value or a simple existence check. Use JOINs when you need columns from both tables in your output. Performance is often similar because the query planner usually optimizes both to the same plan. Choose whichever is more readable for your specific case.

exists_subquery.sql
sql
-- EXISTS: sellers with at least one high-value item (> 500)
SELECT
  s.seller_id,
  s.seller_city
FROM sellers s
WHERE EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.seller_id = s.seller_id
    AND oi.price > 500
);

EXISTS checks if the subquery returns any rows at all. It is a correlated subquery because it references the outer table (s.seller_id).

Fill in the blanks: Complete the subquery

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Products above category average

Loading practice…