Subqueries By rows returned single-row · = < > <= >= <>

Single-Row Subquery

A subquery that returns exactly one row, compared against with the regular operators. Find the maximum, the average-driven filter, the lookup by unique key — all canonical single-row patterns.

What a single-row subquery is

A single-row subquery returns exactly one row to the outer query — typically a single value computed by an aggregate, or a row pinpointed by a unique key. Because there's only one value coming back, you compare against it with the regular operators: =, <, >, <=, >=, <>.

⚠ If the subquery returns more than one row, PostgreSQL raises an error: "more than one row returned by a subquery used as an expression". To handle several rows, switch to a multiple-row subquery using IN, ANY, or ALL.

Syntax

SELECT column_list
FROM table_name
WHERE column = (
    SELECT aggregate_or_unique_column
    FROM other_table
    WHERE ...
);

Demo dataset — books & authors

SQL — Create & insert

CREATE TABLE authors (
    author_id    INT PRIMARY KEY,
    author_name  VARCHAR(50)
);

INSERT INTO authors VALUES
(1, 'Jane Austen'),
(2, 'George Orwell'),
(3, 'J.K. Rowling');

CREATE TABLE books (
    book_id     INT PRIMARY KEY,
    book_title  VARCHAR(80),
    price       DECIMAL(10, 2),
    author_id   INT REFERENCES authors(author_id)
);

INSERT INTO books VALUES
(101, 'Pride and Prejudice',                       24.99, 1),
(102, '1984',                                      19.99, 2),
(103, 'Harry Potter and the Philosopher''s Stone', 29.99, 3),
(104, 'Sense and Sensibility',                     21.99, 1),
(105, 'Animal Farm',                               18.99, 2),
(106, 'Harry Potter and the Chamber of Secrets',   31.99, 3),
(107, 'The Secrets of Dumbledore',                 56.01, 3);

Example 1 — subquery in the SELECT list

For each book, fetch the author's name with a subquery in the SELECT list. The subquery must return exactly one row per outer row.

SQL

SELECT book_title,
       price,
       (SELECT author_name
        FROM   authors a
        WHERE  a.author_id = b.author_id) AS author_name
FROM   books b;

Result

book_title	price	author_name
Pride and Prejudice	24.99	Jane Austen
1984	19.99	George Orwell
Harry Potter and the Philosopher's Stone	29.99	J.K. Rowling
Sense and Sensibility	21.99	Jane Austen
Animal Farm	18.99	George Orwell
Harry Potter and the Chamber of Secrets	31.99	J.K. Rowling
The Secrets of Dumbledore	56.01	J.K. Rowling

💡 A JOIN is usually clearer. The same result is more idiomatic with a JOIN: SELECT b.book_title, b.price, a.author_name FROM books b JOIN authors a USING (author_id); — but the subquery form is sometimes useful when you need an aggregate alongside other columns.

Example 2 — subquery in WHERE with =

Find the most expensive book, using an aggregate inside a subquery.

SQL

SELECT book_title, price
FROM   books
WHERE  price = (SELECT MAX(price) FROM books);

Result

book_title	price
The Secrets of Dumbledore	56.01

Example 3 — books priced above the average

SQL

SELECT book_title, price
FROM   books
WHERE  price > (SELECT AVG(price) FROM books);

The subquery returns the single number 26.28…, and the outer query keeps every book priced higher than that.

Example 4 — subquery in HAVING

Show authors whose total book sales (sum of prices) exceed the average author's total.

SQL

SELECT author_id,
       SUM(price) AS total
FROM   books
GROUP  BY author_id
HAVING SUM(price) > (
    SELECT AVG(author_total)
    FROM   (SELECT SUM(price) AS author_total FROM books GROUP BY author_id) t
);

Operators that work with single-row subqueries

Operator	Meaning
=	Equal to the returned value
<> or !=	Not equal
> / >=	Greater than / greater or equal
< / <=	Less than / less or equal

Recap

A single-row subquery returns exactly one row of one column.
Compared with the standard operators: =, <, >, <=, >=, <>.
Returning multiple rows raises a runtime error.
Returning zero rows yields NULL — the outer comparison then evaluates to UNKNOWN, and no rows match.
Aggregates (MAX, MIN, AVG, SUM, COUNT) are the most common producers of single-row subqueries.