PostgreSQL Subqueries: Scalar, Correlated, EXISTS, and CTEs

Most WHERE clauses you write compare a column to something you already know: price > 20, city = 'Rotterdam'. Sooner or later you hit a question where the comparison value isn’t something you know in advance — it’s itself the answer to a query. “Records priced above the average price.” “Customers who spent more than the average customer.” That’s the moment a beginner SQL toolkit runs out, and subqueries are what fill the gap.

This is also where a lot of people quietly start writing SQL that happens to work rather than SQL they understand — nesting a SELECT inside another SELECT by trial and error until the syntax error goes away. (If you haven’t yet worked through WHERE, JOIN, GROUP BY, and the basics of subqueries, our post on six core SQL skills is a good on-ramp before this one — it introduces subqueries briefly; this guide is the deep dive.) This guide builds a mental model for what a subquery actually is, then works through every shape it can take in PostgreSQL specifically, on one small database you can reproduce yourself.

In PostgreSQL, a subquery is a complete SELECT wrapped in parentheses and dropped in wherever the outer query expects a value, a list, or a table — write it, run it standalone to confirm what it returns, then nest it. Use a scalar subquery (one row, one column) where you’d otherwise type a literal number; use IN/EXISTS where you’d otherwise type a list; use a subquery in FROM where you’d otherwise type a table name. For row-by-row comparisons against something computed per row, use a correlated subquery — but prefer NOT EXISTS over NOT IN for “rows with no match,” since NOT IN silently returns nothing at all if its subquery’s result contains even one NULL.

The Mental Model: Where a Subquery Can Live

A subquery is just a SELECT statement — but where you place it decides what shape its result is allowed to take:

1. In WHERE, as a scalar value. The subquery must return exactly one row and one column — PostgreSQL treats it like a literal you typed in yourself.
2. In WHERE, as a list. Paired with IN, NOT IN, EXISTS, or NOT EXISTS, the subquery can return many rows of one column — a membership test instead of an equality test.
3. In FROM, as a table. The subquery can return many rows and many columns; give it an alias and query it exactly like any other table.

Cutting across all three: a subquery is either standalone (runs once, independent of the outer query) or correlated (references a column from the outer query, so PostgreSQL re-evaluates it once per outer row). Everything below is one of these three placements, in either flavor.

A Record-Shop Database You Can Reproduce

Imagine a small independent vinyl record shop, Wax & Wave, with a handful of regular customers and a shelf of records across four genres. No download needed — build it directly in PostgreSQL:

import psycopg2

conn = psycopg2.connect(host="/tmp/pgsock-subq", port=5435, user="postgres", dbname="waxwave")
conn.autocommit = True
cur = conn.cursor()

cur.execute("""
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    joined_on DATE NOT NULL
);

CREATE TABLE records (
    record_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    artist TEXT NOT NULL,
    genre TEXT NOT NULL,
    price NUMERIC(5,2) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    record_id INTEGER NOT NULL REFERENCES records(record_id),
    quantity INTEGER NOT NULL,
    order_date DATE NOT NULL
);
""")

cur.execute("""
INSERT INTO customers (name, city, joined_on) VALUES
    ('Ana',   'Rotterdam',  '2025-09-02'),
    ('Bram',  'Utrecht',    '2025-09-20'),
    ('Chiara','Rotterdam',  '2025-10-05'),
    ('Dax',   'The Hague',  '2025-10-22'),
    ('Els',   'Utrecht',    '2025-11-14'),
    ('Femke', 'Rotterdam',  '2025-12-01'),
    ('Gijs',  'The Hague',  '2026-01-10'),
    ('Hana',  'Utrecht',    '2026-02-18');

INSERT INTO records (title, artist, genre, price) VALUES
    ('Midnight Blue Room',    'Theo Voss',                   'Jazz',    24.50),
    ('Low Tide Sessions',     'Mireille Coen',                'Jazz',    27.00),
    ('Brass and Rain',        'The Voss Quartet',             'Jazz',    22.00),
    ('Copper Wire Grooves',   'Delta Static',                 'Funk',    21.50),
    ('Warehouse Funk Vol. 2', 'Nine Live Horns',               'Funk',    19.00),
    ('Slow Bass Sunday',      'Delta Static',                 'Funk',    23.50),
    ('Mirror Ball Diaries',   'Lunar Parade',                 'Disco',   20.00),
    ('Satin Nights',          'Sable and the Strings',        'Disco',   25.00),
    ('Roller Rink Redux',     'Lunar Parade',                 'Disco',   18.50),
    ('Slow Weather',          'Field Recordings Collective',  'Ambient', 16.00),
    ('Glass Corridor',        'Noor Ilves',                   'Ambient', 29.00),
    ('Static Tide',           'Field Recordings Collective',  'Ambient', 17.50);
""")

Eight customers, twelve records. Orders are generated with a seeded random number generator so your numbers match mine, including a detail that matters later: about 15% of orders are walk-in cash sales with no loyalty account attached, so orders.customer_id is NULL for those rows, and Hana never orders anything at all.

import random

rng = random.Random(42)
customer_ids = list(range(1, 8))   # Hana (8) deliberately excluded — never orders
record_ids = list(range(1, 13))

rows = []
for i in range(45):
    record_id = rng.choice(record_ids)
    quantity = rng.choices([1, 2, 3], weights=[0.6, 0.3, 0.1])[0]
    month, day = rng.choice([3, 4, 5, 6]), rng.randint(1, 28)
    order_date = f"2026-{month:02d}-{day:02d}"
    customer_id = None if rng.random() < 0.15 else rng.choice(customer_ids)
    rows.append((customer_id, record_id, quantity, order_date))

cur.executemany(
    "INSERT INTO orders (customer_id, record_id, quantity, order_date) VALUES (%s, %s, %s, %s)",
    rows,
)

cur.execute("""
SELECT (SELECT count(*) FROM customers) AS customers,
       (SELECT count(*) FROM records) AS records,
       (SELECT count(*) FROM orders) AS orders,
       (SELECT count(*) FROM orders WHERE customer_id IS NULL) AS walk_in_orders;
""")
cur.fetchall()

[(8, 12, 45, 5)]

Forty-five orders, five of them anonymous walk-ins. (The outputs in this post come from PostgreSQL 14 via psycopg2 2.9 — the SQL itself works on any recent PostgreSQL version.)

Scalar Subqueries: A Query That Returns One Value

The simplest shape: a subquery that returns exactly one row and one column, used anywhere PostgreSQL expects a single value. Which records are priced above the shop’s average?

cur.execute("""
SELECT title, artist, genre, price
FROM records
WHERE price > (SELECT AVG(price) FROM records)
ORDER BY price DESC;
""")
cur.fetchall()

[('Glass Corridor', 'Noor Ilves', 'Ambient', Decimal('29.00')),
 ('Low Tide Sessions', 'Mireille Coen', 'Jazz', Decimal('27.00')),
 ('Satin Nights', 'Sable and the Strings', 'Disco', Decimal('25.00')),
 ('Midnight Blue Room', 'Theo Voss', 'Jazz', Decimal('24.50')),
 ('Slow Bass Sunday', 'Delta Static', 'Funk', Decimal('23.50')),
 ('Brass and Rain', 'The Voss Quartet', 'Jazz', Decimal('22.00'))]

(SELECT AVG(price) FROM records) runs first, collapses all twelve prices to one number (21.96, as it happens), and the outer WHERE treats that number exactly like price > 21.96. PostgreSQL doesn’t care that it came from a nested query instead of you typing the number in — which is also the catch: if that inner query ever returned more than one row, PostgreSQL wouldn’t guess which one to use (the gotchas section below shows exactly what happens).

Subqueries as a List: IN and NOT IN

Pair a subquery with IN and it stops meaning “equals this one value” and starts meaning “is a member of this list.” Which records has a Rotterdam customer ever bought?

cur.execute("""
SELECT DISTINCT r.title, r.genre
FROM records r
WHERE r.record_id IN (
    SELECT o.record_id
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE c.city = 'Rotterdam'
)
ORDER BY r.title;
""")
cur.fetchall()

[('Copper Wire Grooves', 'Funk'), ('Glass Corridor', 'Ambient'), ('Low Tide Sessions', 'Jazz'),
 ('Midnight Blue Room', 'Jazz'), ('Mirror Ball Diaries', 'Disco'), ('Slow Bass Sunday', 'Funk'),
 ('Slow Weather', 'Ambient'), ('Static Tide', 'Ambient'), ('Warehouse Funk Vol. 2', 'Funk')]

Nine of the twelve records show up. NOT IN flips the test — which records has no Rotterdam customer ever bought? Same inner subquery, just negated:

cur.execute("""
SELECT title, genre
FROM records
WHERE record_id NOT IN (
    SELECT o.record_id
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE c.city = 'Rotterdam'
)
ORDER BY title;
""")
cur.fetchall()

[('Brass and Rain', 'Jazz'), ('Roller Rink Redux', 'Disco'), ('Satin Nights', 'Disco')]

The three that are left over are exactly the complement of the nine above. This particular NOT IN is safe because orders.record_id can never be NULL — hang onto that detail, because it’s the one thing standing between NOT IN working correctly and silently breaking, as the gotchas section below demonstrates.

Correlated Subqueries: One Question per Row

Every subquery so far ran once, independent of the outer query. A correlated subquery references a column from the outer row, so PostgreSQL re-evaluates it once for every row the outer query considers. Which individual orders were bigger than that same customer’s usual order size?

cur.execute("""
SELECT o.order_id, c.name, o.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.quantity > (
    SELECT AVG(o2.quantity)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
)
ORDER BY c.name, o.order_id;
""")
cur.fetchall()

[(2, 'Ana', 2), (9, 'Ana', 2), (29, 'Ana', 3), (13, 'Bram', 2), (23, 'Bram', 2), (5, 'Chiara', 2),
 (25, 'Els', 2), (30, 'Els', 2), (32, 'Femke', 2), (10, 'Gijs', 2), (22, 'Gijs', 2), (35, 'Gijs', 2)]

The inner SELECT AVG(o2.quantity) FROM orders o2 WHERE o2.customer_id = o.customer_id can’t run on its own — o.customer_id only exists because the outer query is currently looking at a specific row. Twelve rows come back, and every customer’s threshold is their own average, not the shop’s. That per-row re-evaluation is the whole point of a correlated subquery: it lets you compare a row to something computed about that row’s own group, without a separate GROUP BY pass first.

EXISTS and NOT EXISTS: Testing Rather Than Comparing

EXISTS is a correlated subquery that never returns a value at all — it only asks “does at least one matching row exist?” and returns TRUE or FALSE. Which customers have never placed a single order?

cur.execute("""
SELECT name, city
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY name;
""")
cur.fetchall()

[('Hana', 'Utrecht')]

Only Hana. Notice the inner query selects a meaningless literal 1 — EXISTS never looks at what the subquery returns, only whether it returns anything, so SELECT 1 (or SELECT *) is the conventional way to write it. Because EXISTS tests presence rather than comparing values, it never has to worry about a NULL value showing up in a result list — which is exactly the property that makes it the safe alternative to NOT IN, coming up in the gotchas section.

Subqueries as Tables: Derived Tables in FROM

A subquery in FROM produces a full, temporary table — give it an alias and everything downstream can query it like any other table. Which records are priced above their own genre’s average, not the shop-wide average from the first section?

cur.execute("""
SELECT r.title, r.genre, r.price, genre_avg.avg_price
FROM records r
JOIN (
    SELECT genre, ROUND(AVG(price), 2) AS avg_price
    FROM records
    GROUP BY genre
) AS genre_avg ON genre_avg.genre = r.genre
WHERE r.price > genre_avg.avg_price
ORDER BY r.genre, r.price DESC;
""")
cur.fetchall()

[('Glass Corridor', 'Ambient', Decimal('29.00'), Decimal('20.83')),
 ('Satin Nights', 'Disco', Decimal('25.00'), Decimal('21.17')),
 ('Slow Bass Sunday', 'Funk', Decimal('23.50'), Decimal('21.33')),
 ('Copper Wire Grooves', 'Funk', Decimal('21.50'), Decimal('21.33')),
 ('Low Tide Sessions', 'Jazz', Decimal('27.00'), Decimal('24.50'))]

genre_avg never exists as a real table — PostgreSQL builds it, joins records to it on genre, then discards it once the query finishes. The PostgreSQL subquery documentation covers the full grammar for every subquery form shown in this post, including a few less common ones like ANY and ALL. This is a non-correlated subquery even though it lives in FROM — nothing inside it references the outer records r — which is what makes it possible to plan and run once, rather than once per row.

Common Table Expressions: Naming the Subquery

A WITH clause — a common table expression, or CTE — does the same job as a derived table in FROM, but names it up front so the rest of the query reads like plain English instead of a nested block. Same question as above, rewritten:

cur.execute("""
WITH genre_stats AS (
    SELECT genre, ROUND(AVG(price), 2) AS avg_price
    FROM records
    GROUP BY genre
)
SELECT r.title, r.genre, r.price, gs.avg_price,
       ROUND(r.price - gs.avg_price, 2) AS price_vs_genre_avg
FROM records r
JOIN genre_stats gs ON gs.genre = r.genre
WHERE r.price > gs.avg_price
ORDER BY r.genre, r.price DESC;
""")
cur.fetchall()

[('Glass Corridor', 'Ambient', Decimal('29.00'), Decimal('20.83'), Decimal('8.17')),
 ('Satin Nights', 'Disco', Decimal('25.00'), Decimal('21.17'), Decimal('3.83')),
 ('Slow Bass Sunday', 'Funk', Decimal('23.50'), Decimal('21.33'), Decimal('2.17')),
 ('Copper Wire Grooves', 'Funk', Decimal('21.50'), Decimal('21.33'), Decimal('0.17')),
 ('Low Tide Sessions', 'Jazz', Decimal('27.00'), Decimal('24.50'), Decimal('2.50'))]

Identical rows, but genre_stats is now a name you can reference — and refer to more than once, or build on. CTEs chain: a second WITH block can query the first one by name, one step at a time, rather than nesting subqueries three levels deep:

cur.execute("""
WITH genre_stats AS (
    SELECT genre, ROUND(AVG(price), 2) AS avg_price
    FROM records
    GROUP BY genre
),
above_average AS (
    SELECT r.record_id, r.title, r.genre, r.price
    FROM records r
    JOIN genre_stats gs ON gs.genre = r.genre
    WHERE r.price > gs.avg_price
)
SELECT genre, COUNT(*) AS records_above_avg
FROM above_average
GROUP BY genre
ORDER BY genre;
""")
cur.fetchall()

[('Ambient', 1), ('Disco', 1), ('Funk', 2), ('Jazz', 1)]

above_average is built entirely out of genre_stats, by name, with no re-nesting — each step only has to make sense on its own, which is the real advantage of a CTE over a deeply nested nested subquery: not performance, but that a reader (including future you) can follow the logic one named block at a time.

Three Gotchas Worth Knowing

NOT IN silently returns nothing if its subquery contains even one NULL. Rewrite the “customers who never ordered” question from the EXISTS section using NOT IN instead, and it looks reasonable but breaks:

cur.execute("""
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders)
ORDER BY name;
""")
cur.fetchall()

[]

Zero rows — even though Hana genuinely never ordered anything. The five walk-in orders have customer_id IS NULL, so the subquery’s result list contains a NULL, and SQL’s three-valued logic means x NOT IN (a, b, NULL) is never TRUE for any x, no matter what a and b are — the whole comparison collapses to unknown. Filter the NULL out explicitly and it works again:

cur.execute("""
SELECT name FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
)
ORDER BY name;
""")
cur.fetchall()

[('Hana',)]

But the safer habit is the one from the EXISTS section: reach for NOT EXISTS by default for “rows with no match,” since it never inspects the subquery’s values and so can’t be tripped up by a stray NULL in the first place.

A scalar subquery must return exactly one row, or PostgreSQL raises an error. It’s an easy mistake to make once genres have more than one record in them:

try:
    cur.execute("""
    SELECT title FROM records
    WHERE price = (SELECT price FROM records WHERE genre = 'Jazz');
    """)
    cur.fetchall()
except Exception as e:
    conn.rollback()
    print(f"{type(e).__name__}: {e}")

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

Three Jazz records exist at three different prices, so (SELECT price FROM records WHERE genre = 'Jazz') can’t collapse to one value, and PostgreSQL refuses to guess. Any time you write a scalar subquery, run the inner SELECT by itself first and confirm it returns one row — if the condition inside it could ever match more than one, you need IN or a join instead.

A correlated subquery re-runs once per outer row — it isn’t always the cheapest way to ask the question. The “orders above this customer’s own average” query from earlier can be rewritten as a join against a pre-aggregated derived table, with an identical result:

cur.execute("""
SELECT o.order_id, c.name, o.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN (
    SELECT customer_id, AVG(quantity) AS avg_quantity
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) cust_avg ON cust_avg.customer_id = o.customer_id
WHERE o.quantity > cust_avg.avg_quantity
ORDER BY c.name, o.order_id;
""")
cur.fetchall()

[(2, 'Ana', 2), (9, 'Ana', 2), (29, 'Ana', 3), (13, 'Bram', 2), (23, 'Bram', 2), (5, 'Chiara', 2),
 (25, 'Els', 2), (30, 'Els', 2), (32, 'Femke', 2), (10, 'Gijs', 2), (22, 'Gijs', 2), (35, 'Gijs', 2)]

Same twelve rows. The correlated version asks “what’s this customer’s average?” separately for every order row it considers; the derived-table version computes every customer’s average exactly once with a single grouped aggregate, then joins the result once. On a small table like this one the difference is invisible, but on a large one it’s worth knowing that a correlated subquery isn’t the only way to write a per-group comparison — reach for it when the logic is genuinely row-by-row, and reach for a join or CTE when it isn’t.

Wrapping Up

Every subquery in this post is one of three placements, in one of two flavors:

• Scalar, in WHERE — stands in for a single value; must return exactly one row
• List, in WHERE — paired with IN/NOT IN or EXISTS/NOT EXISTS for membership tests; prefer EXISTS for “no match” questions
• Table, in FROM — a derived table, or the same thing named with WITH as a CTE for readability and chaining
• Correlated vs. standalone — cuts across all three; correlated subqueries reference the outer row and re-run per row

If you want more practice with this exact material — scalar, multi-row, correlated subqueries, and views, building up to a guided project — the Subqueries, CTEs, and Views lessons in our free SQL & Databases course pick up right where this post leaves off. And if you’re loading data into PostgreSQL from Python rather than typing it in directly, loading CSV data into PostgreSQL covers the psycopg2 side of that in depth.