⊂

PostgreSQL Subquery Expressions — EXISTS, IN, ANY, ALL

Complete reference for PostgreSQL subquery expressions including EXISTS, IN, ANY, ALL, SOME, scalar subqueries, and LATERAL joins. Every expression includes syntax, correlated subquery examples, and performance comparisons with equivalent JOINs. Updated for PostgreSQL 16.

5 functions

Jump to function

ANY / ALL (subquery)EXISTS IN (subquery)LATERAL Scalar Subquery

What are PostgreSQL Subquery Expressions — EXISTS, IN, ANY, ALL?

PostgreSQL subquery expressions allow embedding one query inside another for filtering, value comparison, and row generation. EXISTS checks for the existence of any matching row and short-circuits on the first match, IN tests set membership, and ANY/ALL compare a value against all values in a subquery result set. Scalar subqueries return a single value and can appear anywhere a scalar expression is expected, including SELECT lists and WHERE clauses.

ANY / ALL (subquery)

PG 7.4+→ boolean

Compares a value against all results from a subquery. ANY returns true if the comparison holds for any row; ALL if it holds for every row.

DeveloperData Eng

Signatures

value op ANY ( subquery ) → boolean

value op ALL ( subquery ) → boolean

Parameters

Parameter	Type	Description
value	any	Value to compare
op	comparison operator	Any comparison operator: =, <, >, <=, >=, <>
subquery	SELECT returning single column	Set of values

Examples

sql

SELECT * FROM products WHERE price > ANY(SELECT price FROM products WHERE category = 'budget');

→Products pricier than at least one budget item

sql

SELECT * FROM products WHERE price < ALL(SELECT price FROM products WHERE category = 'premium');

→Products cheaper than all premium items

sql

SELECT employee_id, salary FROM employees WHERE salary >= ALL(SELECT salary FROM employees WHERE department_id = 3);

→Employees earning at least as much as every employee in department 3

sql

SELECT id, score FROM submissions WHERE score > ANY(SELECT score FROM submissions WHERE status = 'passing' AND contest_id = 5);

→Submissions beating at least one passing score in contest 5

⚠Anti-Pattern— Confusing <> ANY with NOT IN semantics

`x <> ANY(subquery)` is true when x differs from at least one row — this is almost always true for any non-empty set, not what developers intend when trying to express 'not in the set'. The correct NOT IN equivalent is `<> ALL(subquery)`.

✓ Instead: Use `x <> ALL(subquery)` to express 'x is not equal to any value in the set' — the correct SQL equivalent of `NOT IN`. Use `<> ANY` only when you explicitly mean 'differs from at least one element'.

`x > ALL(subquery)` is equivalent to `x > (SELECT max(col) FROM ...)` but more readable. Similarly, `x > ANY(subquery)` = `x > (SELECT min(col) FROM ...)`. The scalar subquery form is often faster.

example

SELECT * FROM products WHERE price > (SELECT max(price) FROM products WHERE is_sale = true);

→Products priced above the highest sale price — cleaner and faster than ALL

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

NOT IN with a subquery that returns any NULL always returns empty set

If a NOT IN subquery returns even one NULL value, the entire NOT IN returns no rows — silently.

EXISTS

PG 7.4+→ boolean

Returns true if the subquery returns any rows. Short-circuits on first match — does not evaluate all rows.

DeveloperData Eng

Signature

EXISTS ( subquery ) → boolean

Parameters

Parameter	Type	Description
subquery	SELECT statement	Subquery to test

Examples

sql

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

→Users who have ordered

sql

SELECT * FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.product_id = p.id);

→Products never ordered

sql

SELECT department_id FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > 100000);

→Departments containing at least one high-paid employee

sql

UPDATE inventory SET reorder_flag = true WHERE NOT EXISTS (SELECT 1 FROM purchase_orders po WHERE po.sku = inventory.sku AND po.status = 'open');

→Flags items with no open purchase order for reorder

⚠Anti-Pattern— Using COUNT(*) > 0 for existence checks instead of EXISTS

`SELECT COUNT(*) FROM t WHERE condition` scans every matching row to produce a total. `EXISTS` stops at the first match — it is O(1) per outer row when a suitable index exists versus O(n) for COUNT.

✓ Instead: Replace `WHERE (SELECT count(*) FROM orders WHERE user_id = u.id) > 0` with `WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)` for dramatically better performance on large tables.

The SELECT list inside EXISTS is never evaluated — only row existence matters. Use `SELECT 1` (or `SELECT *`) as a convention. The planner optimizes EXISTS to a semi-join.

example

-- Good: 
WHERE EXISTS (SELECT 1 FROM t WHERE condition)
-- Avoid: WHERE EXISTS (SELECT expensive_func() FROM t WHERE condition)

→exists() column list is ignored by the planner

Common Gotchas

⚠

NOT IN with a subquery that returns any NULL always returns empty set

If a NOT IN subquery returns even one NULL value, the entire NOT IN returns no rows — silently.

IN (subquery)

PG 7.4+→ boolean

Tests whether a value equals any result from a subquery. Equivalent to `= ANY(subquery)`.

DeveloperData Eng

Signatures

value IN ( subquery ) → boolean

value NOT IN ( subquery ) → boolean

Parameters

Parameter	Type	Description
value	any	Value to test
subquery	SELECT returning single column	Subquery returning comparison values

Examples

sql

SELECT * FROM orders WHERE user_id IN (SELECT id FROM premium_users);

→Orders from premium users

sql

SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items WHERE order_id = 1);

→Products not in a specific order

sql

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'London');

→Employees located in London departments

sql

SELECT * FROM accounts WHERE id NOT IN (SELECT account_id FROM transactions WHERE transaction_date >= current_date - interval '90 days' AND account_id IS NOT NULL);

→Accounts with no transactions in the last 90 days, NULL-safe

⚠Anti-Pattern— NOT IN with a subquery that can return NULLs returns zero rows

If `SELECT fk FROM t` returns even one NULL, `NOT IN` evaluates to NULL for every outer row — not false, not true — and zero rows are returned. This is one of the most common silent SQL bugs.

✓ Instead: Replace `WHERE id NOT IN (SELECT fk FROM t)` with `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.fk = outer.id)`. If you must use NOT IN, add `WHERE fk IS NOT NULL` inside the subquery.

If the subquery returns any NULLs, `NOT IN` returns NULL (not true) for every row. Always add `WHERE col IS NOT NULL` to the subquery, or use `NOT EXISTS` which handles NULLs correctly.

example

-- Safe NOT IN:
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM bans WHERE user_id IS NOT NULL);
-- Or use NOT EXISTS:
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.user_id = u.id);

→Correctly excludes banned users

Common Gotchas

⚠

NOT IN with a subquery that returns any NULL always returns empty set

If a NOT IN subquery returns even one NULL value, the entire NOT IN returns no rows — silently.

LATERAL

PG 9.3+→ set of rows

A LATERAL subquery or function can reference columns from preceding FROM items. It is evaluated once per row from the preceding item, like a correlated subquery but in the FROM clause.

DeveloperData Eng

Signatures

... FROM table, LATERAL subquery_or_function ...

... FROM table CROSS JOIN LATERAL subquery ...

Parameters

Parameter	Type	Description
subquery	SELECT or set-returning function	Subquery that can reference outer query columns

Examples

sql

SELECT u.id, o.amount FROM users u, LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) AS o;

→Each user's largest order

sql

SELECT u.id, recent.* FROM users u, LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) AS recent;

→3 most recent orders per user

sql

SELECT c.id, c.name, stats.total_spent FROM customers c LEFT JOIN LATERAL (SELECT sum(amount) AS total_spent FROM orders WHERE customer_id = c.id AND status = 'completed') AS stats ON true;

→Total completed spend per customer, including customers with no orders (NULL via LEFT JOIN LATERAL)

sql

SELECT p.id, expanded.tag FROM posts p, LATERAL unnest(p.tags) AS expanded(tag) WHERE expanded.tag ILIKE 'postgres%';

→Explodes the tags array per post and filters to Postgres-related tags

⚠Anti-Pattern— Forgetting LEFT JOIN LATERAL ... ON true when the subquery can return zero rows

Using a bare comma LATERAL join (`FROM t, LATERAL (...)`) drops outer rows for which the lateral subquery returns no results — equivalent to an INNER JOIN. If some rows legitimately have no matching subquery results, they are silently excluded.

✓ Instead: Use `LEFT JOIN LATERAL (...) AS sub ON true` to preserve all outer rows even when the lateral subquery returns zero rows.

LATERAL JOIN is the cleanest way to get 'top N per group': `FROM groups g, LATERAL (SELECT * FROM items WHERE group_id = g.id ORDER BY score DESC LIMIT 3) AS top3`. This is more readable than a PARTITION BY approach.

example

SELECT c.id, top_orders.* FROM customers c, LATERAL (SELECT * FROM orders WHERE customer_id = c.id ORDER BY amount DESC LIMIT 5) AS top_orders;

→Top 5 orders per customer — efficient and readable

Common Gotchas

⚠

NOT IN with a subquery that returns any NULL always returns empty set

If a NOT IN subquery returns even one NULL value, the entire NOT IN returns no rows — silently.

Scalar Subquery

PG 7.4+→ any scalar type

A subquery that returns exactly one row and one column — used anywhere a scalar value is expected. Returns NULL if zero rows match.

DeveloperData Eng

Signature

( SELECT expression FROM ... ) → single value

Parameters

Parameter	Type	Description
subquery	SELECT returning one row, one column	Must return zero or one row

Examples

sql

SELECT name, (SELECT count(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;

→User names with their order count

sql

SELECT id, (SELECT max(amount) FROM orders WHERE user_id = id) AS max_order FROM users;

→Max order amount per user

sql

SELECT product_id, price, price - (SELECT avg(price) FROM products) AS diff_from_avg FROM products;

→Each product's price deviation from the catalogue average

sql

INSERT INTO audit_log (event, snapshot_count) VALUES ('daily_sync', (SELECT count(*) FROM records WHERE synced_at::date = current_date));

→Logs today's synced record count into the audit table

⚠Anti-Pattern— Correlated scalar subquery in SELECT list runs once per outer row

A scalar subquery in the SELECT list executes independently for each row in the outer query. For a table with 100,000 rows, this means 100,000 separate executions — an O(n) query-within-a-query that becomes catastrophically slow.

✓ Instead: Replace `SELECT u.id, (SELECT count(*) FROM orders WHERE user_id = u.id) FROM users u` with `SELECT u.id, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id`.

A scalar subquery in the SELECT list runs once for every row in the outer query. For large tables this is O(n) queries. Replace with a LEFT JOIN + GROUP BY or a lateral join for much better performance.

example

-- Slow (correlated):
SELECT u.id, (SELECT count(*) FROM orders WHERE user_id = u.id) FROM users u;
-- Fast (join):
SELECT u.id, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;

→Same result, dramatically better performance

Common Gotchas

⚠

NOT IN with a subquery that returns any NULL always returns empty set

If a NOT IN subquery returns even one NULL value, the entire NOT IN returns no rows — silently.

PostgreSQL Subquery Expressions — EXISTS, IN, ANY, ALL

Jump to function

What are PostgreSQL Subquery Expressions — EXISTS, IN, ANY, ALL?

ANY / ALL (subquery)

Signatures

Parameters

Examples

Common Gotchas

EXISTS

Signature

Parameters

Examples

Common Gotchas

IN (subquery)

Signatures

Parameters

Examples

Common Gotchas

LATERAL

Signatures

Parameters

Examples

Common Gotchas

Scalar Subquery

Signature

Parameters

Examples

Common Gotchas

Related PostgreSQL Categories