Subqueries

EXISTS, IN (subquery), CTEs, and FROM subquery — for composing complex queries from simpler building blocks.

#When to use subqueries

Subqueries let you use the result of one query inside another. This is essential for questions like "find users who have at least one published post" or "show me only data from the last 7 days." Zanith supports four subquery patterns.

Outer query

WHERE

Subquery

inner query

Result

#EXISTS

The most common subquery pattern. "Find records where a related subquery returns at least one result." More efficient than JOIN when you only need to check existence.

TSusers with published posts

11 lines


1import { exists, notExists } from 'zanith';
2 
3// Users who have at least one published post
4const activeAuthors = await db.user.query()
5  .where(({ user }) =>
6    exists(
7      db.post.query()
8        .where(({ post }) => post.published.eq(true))
9    )
10  )
11  .execute();

#NOT EXISTS

8 lines


1// Users who have never posted anything
2const lurkers = await db.user.query()
3  .where(({ user }) =>
4    notExists(
5      db.post.query()
6    )
7  )
8  .execute();

#IN (subquery)

Check if a field's value appears in the results of another query:

12 lines


1import { inSubquery, notInSubquery } from 'zanith';
2 
3// Users whose IDs appear in the post authors list
4const result = await db.user.query()
5  .where(({ user }) =>
6    inSubquery(
7      user.id,
8      db.post.query()
9        .select(({ post }) => ({ authorId: post.authorId }))
10    )
11  )
12  .execute();

#CTE (WITH clause)

Common Table Expressions let you define a temporary named result set, then query from it. This makes complex queries more readable by breaking them into logical steps.

TSrecent posts CTE

21 lines


1const results = await db.post.query()
2  // Step 1: Define the CTE
3  .withCTE(
4    'recent',
5    "SELECT * FROM posts WHERE created_at > NOW() - INTERVAL '7 days'"
6  )
7  // Step 2: Query FROM the CTE instead of the raw table
8  .fromCTE('recent')
9  // Step 3: Add additional filters
10  .where(({ post }) => post.published.eq(true))
11  .limit(20)
12  .execute();
13 
14// Generated SQL:
15// WITH "recent" AS (
16//   SELECT * FROM posts WHERE created_at > NOW() - INTERVAL '7 days'
17// )
18// SELECT "posts".*
19// FROM "recent" AS "posts"
20// WHERE "posts"."published" = TRUE
21// LIMIT $1

#FROM subquery

Use a raw SQL subquery as the FROM source. Useful when you need a pre-filtered or pre-transformed dataset as the starting point.

13 lines


1const results = await db.post.query()
2  .fromSubquery(
3    'SELECT * FROM posts WHERE published = true',
4    [],         // params (none in this case)
5    'pub'       // alias for the subquery
6  )
7  .where(({ post }) => post.title.contains('guide'))
8  .execute();
9 
10// Generated SQL:
11// SELECT "pub".*
12// FROM (SELECT * FROM posts WHERE published = true) AS "pub"
13// WHERE "pub"."title" ILIKE $1

#Function reference

Function	SQL pattern	Import from
`exists(subquery)`	`WHERE EXISTS (SELECT ...)`	`'zanith'`
`notExists(subquery)`	`WHERE NOT EXISTS (SELECT ...)`	`'zanith'`
`inSubquery(field, subquery)`	`WHERE field IN (SELECT ...)`	`'zanith'`
`notInSubquery(field, subquery)`	`WHERE field NOT IN (SELECT ...)`	`'zanith'`
`.withCTE(name, sql)`	`WITH name AS (sql)`	Query builder method
`.fromCTE(name)`	`FROM name AS alias`	Query builder method
`.fromSubquery(sql, params, alias)`	`FROM (sql) AS alias`	Query builder method