⊂

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

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

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.

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

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

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

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

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

`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

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

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

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

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

PostgreSQL Subquery Expressions — EXISTS, IN, ANY, ALL

Jump to function

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

EXISTS

Signature

Parameters

Examples

IN (subquery)

Signatures

Parameters

Examples

ANY / ALL (subquery)

Signatures

Parameters

Examples

Scalar Subquery

Signature

Parameters

Examples

LATERAL

Signatures

Parameters

Examples

Related PostgreSQL Categories