Writing Subqueries in PostgreSQL

A subquery is a query nested inside another query. It can appear in the WHERE, FROM, or SELECT clause and is a powerful way to break complex logic into manageable steps.

Subquery in WHERE

-- Customers who have placed at least one order
SELECT first_name, last_name, email
FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id FROM orders
);

-- Products priced above the average
SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

The inner query runs first, producing a result set or single value that the outer query uses as a filter. Use IN for sets and standard comparison operators for scalar values.

Subquery in FROM (Derived Table)

-- Top spending customers
SELECT
  sub.customer_id,
  c.first_name,
  sub.total_spent
FROM (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
) sub
JOIN customers c ON sub.customer_id = c.id
ORDER BY sub.total_spent DESC
LIMIT 10;

A subquery in the FROM clause acts as a temporary table. It must be given an alias (here, sub). This pattern is useful when you need to aggregate first and then join.

Correlated Subquery

-- Each customer's most recent order
SELECT c.first_name, c.last_name, o.total, o.created_at
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at = (
  SELECT MAX(o2.created_at)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

A correlated subquery references a column from the outer query. It runs once per outer row, which can be slow on large tables. For better performance, consider using window functions or a lateral join instead.

EXISTS for Existence Checks

-- Customers who have never ordered
SELECT first_name, email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

When to Use Subqueries

• Filtering by the result of an aggregate (e.g., above-average values)
• Checking existence or non-existence of related rows
• Building intermediate result sets before joining
• When CTEs are overkill for a simple one-off nested condition