Parents with at least one childSupported

EXISTS answers "does a related row exist" without joining, so it does not multiply your result rows and it can stop at the first match.

In the builder#

SelectBuilder has nine EXISTS methods — whereExists, andWhereExists, orWhereExists and the NotExists counterparts:

import { trustedTable } from '@zmdb/sql';

import { postgres } from '@zmdb/postgres';

const c = createQueryCompiler(postgres);

const authorsWithPosts = c
  .selectFrom(trustedTable('users'))
  .whereExists(c.selectFrom(trustedTable('posts')).where('author_id', '=', c.ref('users.id')))
  .orderBy('name', 'asc')
  .compile();

The correlation — posts.author_id = users.id — is what makes it a subquery per row rather than a constant. Without it, the subquery is true if _any_ post exists and your filter does nothing.

Why not a join#

// join: a user with 40 posts arrives 40 times
.innerJoin('posts', 'posts.author_id', 'users.id')

// exists: once, and the database can stop at the first post
.whereExists(...)

Use a join when you need columns from the other table, EXISTS when you only need the predicate. Getting this wrong is a common source of "why are there duplicates" and of DISTINCT being added to paper over it — which then forces a sort.

NOT EXISTS#

Users who have never posted:

import { trustedTable } from '@zmdb/sql';

c.selectFrom(trustedTable('users'))
  .whereNotExists(c.selectFrom(trustedTable('posts')).where('author_id', '=', c.ref('users.id')))
  .compile();

In the DTO API#

Every FieldOps operator accepts a SubqueryTarget, so a repository call can carry a subquery:

import { trustedTable } from '@zmdb/sql';

await userRepo.find({
  id: { in: c.selectFrom(trustedTable('posts')).select(['author_id']).where('published', '=', true) },
});

That is IN (subquery) rather than EXISTS, and for a moderate number of ids it performs comparably. There is no exists: key in FieldOps — for a correlated EXISTS at the DTO level, drop to the builder.

Combining with other filters#

import { trustedTable } from '@zmdb/sql';

c.selectFrom(trustedTable('users'))
  .where('active', '=', true)
  .andWhereExists(c.selectFrom(trustedTable('orders')).where('user_id', '=', c.ref('users.id')).where('total', '>', 100))
  .compile();

Note andWhereExists, not whereExists, once a predicate is already present — and remember the builder is immutable, so each call must be chained or reassigned.

Index the correlated column#

posts.author_id needs an index, or every outer row causes a scan of posts:

createIndexDdl({ name: 'posts_author', table: 'posts', columns: ['author_id'] }, 'postgres');

This is the difference between EXISTS being the fast option and being the slow one. Check with EXPLAIN ANALYZE — see Query Performance.

EXISTS versus IN versus a join#

Modern Postgres and MySQL planners often rewrite between these, so the difference is smaller than folklore suggests — but the row-multiplication difference with a join is not a planner detail, it changes your results.

---

See also: Subqueries · Joins · Query Performance