Subqueries

Use subqueries in Drizzle ORM for complex nested queries, correlated subqueries, and Common Table Expressions (CTEs) with full type safety.

Drizzle ORM provides powerful support for subqueries, allowing you to nest queries within other queries for complex data retrieval and manipulation.

Basic Subquery

Create and use a subquery:

import { db } from './db';
import { users, posts } from './schema';
import { eq } from 'drizzle-orm';

// Create a subquery
const activeUsers = db.select()
  .from(users)
  .where(eq(users.active, true))
  .as('active_users');

// Use the subquery
const result = await db.select()
  .from(activeUsers);

Subquery in WHERE Clause

Use subqueries for filtering:

IN Subquery

import { sql, inArray } from 'drizzle-orm';

// Users who have posts
const usersWithPosts = await db.select()
  .from(users)
  .where(
    sql`${users.id} IN (
      SELECT DISTINCT ${posts.userId} FROM ${posts}
    )`
  );

// Or using inArray with subquery
const userIds = db.select({ id: posts.userId })
  .from(posts)
  .as('user_ids');

Subquery in SELECT

Include subquery results in select:

import { sql, eq } from 'drizzle-orm';

// Select with correlated subquery
const result = await db.select({
  id: users.id,
  name: users.name,
  postCount: sql<number>`(
    SELECT COUNT(*)
    FROM ${posts}
    WHERE ${posts.userId} = ${users.id}
  )`,
})
  .from(users);

Common Table Expressions (CTE)

Use WITH clauses for readable complex queries:

Basic CTE

import { sql } from 'drizzle-orm';

// Define CTE
const activeUsers = db.$with('active_users').as(
  db.select({
    id: users.id,
    name: users.name,
  })
    .from(users)
    .where(eq(users.active, true))
);

// Use CTE in query
const result = await db.with(activeUsers)
  .select()
  .from(activeUsers);

Subquery Joins

Join with subquery results:

import { sql, eq, desc } from 'drizzle-orm';

// Get users with their latest post
const latestPosts = db.select({
  userId: posts.userId,
  title: posts.title,
  createdAt: posts.createdAt,
  rowNum: sql<number>`ROW_NUMBER() OVER (PARTITION BY ${posts.userId} ORDER BY ${posts.createdAt} DESC)`.as('row_num'),
})
  .from(posts)
  .as('latest_posts');

const result = await db.select({
  userName: users.name,
  latestPostTitle: latestPosts.title,
})
  .from(users)
  .innerJoin(latestPosts, eq(users.id, latestPosts.userId))
  .where(eq(latestPosts.rowNum, 1));

Correlated Subqueries

Subqueries that reference outer query:

import { sql, gt } from 'drizzle-orm';

// Users with above-average post count
const result = await db.select({
  id: users.id,
  name: users.name,
  postCount: sql<number>`(
    SELECT COUNT(*)
    FROM ${posts}
    WHERE ${posts.userId} = ${users.id}
  )`,
})
  .from(users)
  .where(
    sql`(
      SELECT COUNT(*)
      FROM ${posts}
      WHERE ${posts.userId} = ${users.id}
    ) > (
      SELECT AVG(post_count)
      FROM (
        SELECT COUNT(*) as post_count
        FROM ${posts}
        GROUP BY ${posts.userId}
      ) as counts
    )`
  );

Subquery with Aggregations

Combine subqueries with aggregate functions:

import { count, sum, avg, sql } from 'drizzle-orm';

// Department statistics
const departmentStats = db.select({
  department: employees.department,
  avgSalary: avg(employees.salary),
  employeeCount: count(),
})
  .from(employees)
  .groupBy(employees.department)
  .as('dept_stats');

const result = await db.select({
  department: departmentStats.department,
  avgSalary: departmentStats.avgSalary,
  employeeCount: departmentStats.employeeCount,
  aboveCompanyAvg: sql<boolean>`${departmentStats.avgSalary} > (
    SELECT AVG(${employees.salary}) FROM ${employees}
  )`,
})
  .from(departmentStats);

Lateral Joins (PostgreSQL)

Use LATERAL for more powerful correlated subqueries:

import { sql, desc } from 'drizzle-orm';

// Get each user with their 3 most recent posts
const recentPosts = db.select({
  id: posts.id,
  title: posts.title,
  createdAt: posts.createdAt,
})
  .from(posts)
  .where(eq(posts.userId, users.id))
  .orderBy(desc(posts.createdAt))
  .limit(3)
  .as('recent_posts');

const result = await db.select({
  userId: users.id,
  userName: users.name,
  postTitle: recentPosts.title,
})
  .from(users)
  .leftJoinLateral(recentPosts, sql`true`);

Subquery in FROM Clause

Use subquery as a data source:

import { sql } from 'drizzle-orm';

// Aggregate over grouped results
const userPostCounts = db.select({
  userId: posts.userId,
  postCount: count(),
})
  .from(posts)
  .groupBy(posts.userId)
  .as('user_post_counts');

const result = await db.select({
  avgPostsPerUser: avg(userPostCounts.postCount),
  maxPostsPerUser: max(userPostCounts.postCount),
})
  .from(userPostCounts);

Nested Subqueries

Subqueries within subqueries:

import { sql } from 'drizzle-orm';

// Multi-level nesting
const topUsers = db.select({
  userId: posts.userId,
  postCount: count(),
})
  .from(posts)
  .groupBy(posts.userId)
  .having(({ postCount }) => gt(postCount, 10))
  .as('top_users');

const topUsersWithDetails = db.select({
  id: users.id,
  name: users.name,
  postCount: topUsers.postCount,
})
  .from(users)
  .innerJoin(topUsers, eq(users.id, topUsers.userId))
  .as('top_users_details');

const result = await db.select()
  .from(topUsersWithDetails)
  .where(gt(topUsersWithDetails.postCount, 20));

Scalar Subqueries

Subqueries returning a single value:

import { sql } from 'drizzle-orm';

// Compare to scalar value from subquery
const result = await db.select()
  .from(products)
  .where(
    sql`${products.price} > (
      SELECT AVG(${products.price}) FROM ${products}
    )`
  );

Insert from Subquery

Insert data from a select query:

import { sql } from 'drizzle-orm';

// Archive old records
await db.insert(archivedUsers)
  .select(
    db.select()
      .from(users)
      .where(lt(users.lastLoginAt, sql`now() - interval '1 year'`))
  );

Update with Subquery

Update using subquery results:

import { sql } from 'drizzle-orm';

// Update with calculated value
await db.update(users)
  .set({
    postCount: sql`(
      SELECT COUNT(*)
      FROM ${posts}
      WHERE ${posts.userId} = ${users.id}
    )`,
  });

Delete with Subquery

Delete based on subquery:

import { sql, inArray } from 'drizzle-orm';

// Delete users who haven't posted
await db.delete(users)
  .where(
    notExists(
      db.select().from(posts).where(eq(posts.userId, users.id))
    )
  );

Union with Subqueries

Combine multiple subquery results:

// Combine different user types
const admins = db.select({
  id: users.id,
  name: users.name,
  type: sql<string>`'admin'`,
})
  .from(users)
  .where(eq(users.role, 'admin'))
  .as('admins');

const moderators = db.select({
  id: users.id,
  name: users.name,
  type: sql<string>`'moderator'`,
})
  .from(users)
  .where(eq(users.role, 'moderator'))
  .as('moderators');

const result = await db.select().from(admins)
  .unionAll(db.select().from(moderators));

Type Safety

Subqueries maintain full type safety:

const activeUsers = db.select({
  id: users.id,
  name: users.name,
  email: users.email,
})
  .from(users)
  .where(eq(users.active, true))
  .as('active_users');

const result = await db.select()
  .from(activeUsers);

// TypeScript knows the shape:
// result: Array<{
//   id: number;
//   name: string;
//   email: string;
// }>

Performance Tips

1. Use CTEs for Readability

   CTEs make complex queries easier to understand and maintain

2. Index Subquery Columns

   Ensure columns used in subquery joins and WHERE clauses are indexed

3. Limit Subquery Results

   Use LIMIT in subqueries when you don’t need all rows

4. Consider Materialized CTEs

   For expensive subqueries used multiple times, consider materialized views

5. Test Query Plans

   Use EXPLAIN to understand how the database executes your subqueries

Common Patterns

Top N per Group

const ranked = db.select({
  ...posts,
  rank: sql`ROW_NUMBER() OVER (
    PARTITION BY ${posts.userId} 
    ORDER BY ${posts.createdAt} DESC
  )`,
})
  .from(posts)
  .as('ranked');

await db.select()
  .from(ranked)
  .where(eq(ranked.rank, 1));

Deduplication

await db.delete(users)
  .where(sql`id NOT IN (
    SELECT MIN(id)
    FROM ${users}
    GROUP BY email
  )`);

Running Totals

const sales = db.select({
  date: orders.date,
  amount: orders.amount,
  runningTotal: sql`SUM(${orders.amount}) OVER (
    ORDER BY ${orders.date}
  )`,
})
  .from(orders);

Pivot Tables

const pivot = db.select({
  category: products.category,
  q1Sales: sum(sql`CASE WHEN quarter = 1 THEN amount END`),
  q2Sales: sum(sql`CASE WHEN quarter = 2 THEN amount END`),
})
  .from(products)
  .groupBy(products.category);

Next Steps

Joins

Combine subqueries with joins

Aggregations

Use aggregations in subqueries

Select Queries

Master the select query builder

Performance

Optimize subquery performance