PostgreSQL Subqueries - PostgreSQL Tutorial

A subquery is a query inside another query.

In WHERE

SQL

-- books priced above average
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

IN

SQL

SELECT title FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'Nepal');

EXISTS

Usually the best choice for "does a related row exist" — it stops at the first match and handles NULLs safely:

SQL

SELECT a.name
FROM authors a
WHERE EXISTS (
    SELECT 1 FROM books b WHERE b.author_id = a.id AND b.price > 1000
);

And the negative form:

SQL

SELECT a.name
FROM authors a
WHERE NOT EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id);

Prefer NOT EXISTS over NOT IN — as covered earlier, NOT IN returns nothing at all if the subquery yields any NULL.

Correlated subqueries

One that references the outer query, evaluated per row. Readable, but can be slow on large tables:

SQL

SELECT
    a.name,
    (SELECT COUNT(*) FROM books b WHERE b.author_id = a.id) AS book_count
FROM authors a;

A LEFT JOIN ... GROUP BY usually performs better for the same result.

In FROM — derived tables

SQL

SELECT author, book_count
FROM (
    SELECT author, COUNT(*) AS book_count
    FROM books
    GROUP BY author
) AS stats
WHERE book_count > 2;

A subquery in FROM must have an alias (AS stats).

Scalar subqueries in SELECT

SQL

SELECT
    title,
    price,
    price - (SELECT AVG(price) FROM books) AS diff_from_avg
FROM books;

A subquery used this way must return exactly one row and one column, or you get an error at runtime.

ANY and ALL

SQL

SELECT * FROM books WHERE price > ALL (SELECT price FROM books WHERE author = 'X');
SELECT * FROM books WHERE price > ANY (SELECT price FROM books WHERE author = 'X');

These are rarely needed — a MAX()/MIN() subquery usually says the same thing more clearly.