Filter parents with children
Return users only when a correlated posts subquery finds a matching post.
Drizzle ORM · Function
exists
exists turns a Drizzle subquery into an SQL EXISTS condition. Use it in where to retain outer rows for which the subquery finds at least one result.
How it works
exists wraps a subquery as an SQL EXISTS predicate. The outer query keeps a row when the subquery produces at least one result, which is useful for testing whether a related record is present.
Syntax
exists(subquery)Parameters
| Name | Type | Description |
|---|---|---|
subquery | SQL query | A Drizzle select query used as the SQL subquery whose result presence is tested. |
Returns
A SQL boolean predicate representing whether the supplied subquery returns any rows.
Key points
- Import
existsfromdrizzle-ormalongside other filter operators. - The function represents the SQL existence condition.
- Pass a Drizzle select query as the subquery argument.
- Apply the predicate in the outer query's
whereclause. - Drizzle emits a SQL
WHERE EXISTSexpression around the supplied subquery.
Example: Filter parents with related rows
import { exists } from "drizzle-orm";
const postsByUser = db
.select({ id: posts.id })
.from(posts)
.where(eq(posts.userId, users.id));
const usersWithPosts = await db
.select()
.from(users)
.where(exists(postsByUser));Selects users for whom the correlated posts subquery finds at least one matching row.
When to use it
Presence checks
Check whether a matching row exists without returning child columns.
Duplicate-free parent filtering
Avoid multiplying parent result rows when several child rows match.
Interview questions
QWhat does exists do?
exists converts a subquery into an existence predicate. The outer row is retained when the subquery returns at least one row, producing SQL such as WHERE EXISTS (SELECT ...).
Follow-up: How would you find users with posts? Answer: correlate the subquery by comparing the child foreign key with the outer user's key, then pass that subquery to exists.
QHow do you select parent rows that have at least one child?
Build a subquery from the child table, correlate it to the outer table with a filter, and apply exists in the outer query's where clause. This returns only parents having a matching child.
Follow-up: What should the subquery select? Answer: only a small marker such as sql\1\ when child data is not needed.
QWhen is exists preferable to an inner join?
Use an inner join when the result must include related child data. Use exists when the requirement is only to test whether a related row is present and return the parent rows.
Follow-up: Why can exists avoid duplicate parents? Answer: it tests presence rather than adding one output row for every matching child.
Related
notExistseqsqlinnerJoinselect