Subqueries

Chapter 19: Subqueries & Advanced Queries

Mastering Subqueries in TypeORM

---

19.1 What is a Subquery?

A subquery is a query nested inside another query. It’s used for complex data retrieval that can’t be achieved with simple joins.

                    Subquery Types
    ================================================================================

                            Subquery
                                |
            +-------------------+-------------------+
            |           |            |              |
            v           v            v              v
        +-------+  +----------+  +--------+  +-----------+
        | SELECT|  | FROM     |  | WHERE  |  | JOIN      |
        | Clause|  | Clause   |  | Clause |  | Clause    |
        +-------+  +----------+  +--------+  +-----------+

    Examples:
    - SELECT (SELECT COUNT(*) FROM users) as user_count
    - FROM (SELECT * FROM users WHERE active = true) active_users
    - WHERE id IN (SELECT userId FROM posts)
    - JOIN (SELECT authorId, COUNT(*) FROM posts GROUP BY authorId) post_counts

    ================================================================================

---

19.2 Subquery in SELECT

Basic Scalar Subquery

import { User } from './user.entity';
import { Post } from './post.entity';

const result = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
  ])
  .addSelect(qb => {
    return qb.subQuery()
      .select('COUNT(*)')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();
  }, 'postCount')
  .getRawMany();

// SQL:
// SELECT user.id, user.name,
//        (SELECT COUNT(*) FROM posts post WHERE post.authorId = user.id) AS "postCount"
// FROM users user

// Returns: [{ id: 1, name: 'John', postCount: '5' }, ...]

Multiple Subqueries in SELECT

const result = await userRepository
  .createQueryBuilder('user')
  .select(['user.id', 'user.name'])
  .addSelect(qb => {
    return qb.subQuery()
      .select('COUNT(*)')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();
  }, 'postCount')
  .addSelect(qb => {
    return qb.subQuery()
      .select('COUNT(*)')
      .from(Comment, 'comment')
      .where('comment.authorId = user.id')
      .getQuery();
  }, 'commentCount')
  .getRawMany();

---

19.3 Subquery in WHERE

IN Subquery

// Find users who have published posts
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('post.authorId')
      .from(Post, 'post')
      .where('post.published = :published')
      .getQuery();

    return `user.id IN ${subQuery}`;
  })
  .setParameter('published', true)
  .getMany();

// SQL:
// SELECT * FROM users user
// WHERE user.id IN (SELECT post.authorId FROM posts post WHERE post.published = true)

NOT IN Subquery

// Find users who have never posted
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('post.authorId')
      .from(Post, 'post')
      .groupBy('post.authorId')
      .getQuery();

    return `user.id NOT IN ${subQuery}`;
  })
  .getMany();

EXISTS Subquery

// Find users with at least one post
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();

    return `EXISTS ${subQuery}`;
  })
  .getMany();

// SQL:
// SELECT * FROM users user
// WHERE EXISTS (SELECT 1 FROM posts post WHERE post.authorId = user.id)

NOT EXISTS Subquery

// Find users without any posts
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();

    return `NOT EXISTS ${subQuery}`;
  })
  .getMany();

Comparison with Subquery

// Find users with above average post count
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('AVG(post_count)')
      .from(
        qb => qb
          .subQuery()
          .select('COUNT(*)', 'post_count')
          .from(Post, 'p')
          .groupBy('p.authorId'),
        'counts'
      )
      .getQuery();

    return `(SELECT COUNT(*) FROM posts WHERE "authorId" = user.id) > ${subQuery}`;
  })
  .getMany();

---

19.4 Subquery in FROM (Derived Table)

Basic Derived Table

const result = await userRepository
  .createQueryBuilder()
  .select('active_users.name', 'name')
  .addSelect('active_users.email', 'email')
  .from(qb => {
    return qb.subQuery()
      .select(['user.name', 'user.email'])
      .from(User, 'user')
      .where('user.isActive = true')
      .getQuery();
  }, 'active_users')
  .getRawMany();

// SQL:
// SELECT active_users.name, active_users.email
// FROM (SELECT user.name, user.email FROM users user WHERE user.isActive = true) active_users

Aggregation with Derived Table

// Get user statistics from derived table
const result = await userRepository
  .createQueryBuilder()
  .select('stats.authorId', 'authorId')
  .addSelect('stats.postCount', 'postCount')
  .addSelect('stats.totalViews', 'totalViews')
  .from(qb => {
    return qb.subQuery()
      .select('post.authorId', 'authorId')
      .addSelect('COUNT(*)', 'postCount')
      .addSelect('SUM(post.viewCount)', 'totalViews')
      .from(Post, 'post')
      .groupBy('post.authorId')
      .getQuery();
  }, 'stats')
  .where('stats.postCount > :min', { min: 0 })
  .getRawMany();

---

19.5 Subquery in JOIN

Join with Derived Table

const result = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'post_stats.postCount',
  ])
  .leftJoin(
    qb => qb
      .subQuery()
      .select('post.authorId', 'authorId')
      .addSelect('COUNT(*)', 'postCount')
      .from(Post, 'post')
      .groupBy('post.authorId'),
    'post_stats',
    'post_stats.authorId = user.id'
  )
  .getRawMany();

// SQL:
// SELECT user.id, user.name, post_stats.postCount
// FROM users user
// LEFT JOIN (
//   SELECT post.authorId, COUNT(*) as postCount
//   FROM posts post
//   GROUP BY post.authorId
// ) post_stats ON post_stats.authorId = user.id

Multiple Subquery Joins

const result = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'post_stats.postCount',
    'comment_stats.commentCount',
  ])
  .leftJoin(
    qb => qb
      .subQuery()
      .select('post.authorId', 'authorId')
      .addSelect('COUNT(*)', 'postCount')
      .from(Post, 'post')
      .groupBy('post.authorId'),
    'post_stats',
    'post_stats.authorId = user.id'
  )
  .leftJoin(
    qb => qb
      .subQuery()
      .select('comment.authorId', 'authorId')
      .addSelect('COUNT(*)', 'commentCount')
      .from(Comment, 'comment')
      .groupBy('comment.authorId'),
    'comment_stats',
    'comment_stats.authorId = user.id'
  )
  .getRawMany();

---

19.6 Correlated Subqueries

A correlated subquery references columns from the outer query.

                    Correlated vs Non-Correlated
    +------------------------------------------------------------------+
    |                                                                   |
    |    Non-Correlated:                                                |
    |    - Executes once for entire query                               |
    |    - Independent of outer query                                   |
    |                                                                   |
    |    SELECT * FROM users                                            |
    |    WHERE id IN (SELECT authorId FROM posts)                       |
    |                                                                   |
    +------------------------------------------------------------------+
    |                                                                   |
    |    Correlated:                                                    |
    |    - Executes once per row in outer query                         |
    |    - References outer query columns                               |
    |                                                                   |
    |    SELECT *,                                                      |
    |      (SELECT COUNT(*) FROM posts WHERE authorId = users.id)       |
    |    FROM users                                                     |
    |                                                                   |
    +------------------------------------------------------------------+

Correlated Subquery Example

// Get each user with their latest post title
const result = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
  ])
  .addSelect(qb => {
    return qb.subQuery()
      .select('post.title')
      .from(Post, 'post')
      .where('post.authorId = user.id')  // References outer query
      .orderBy('post.createdAt', 'DESC')
      .limit(1)
      .getQuery();
  }, 'latestPostTitle')
  .getRawMany();

---

19.7 Common Table Expressions (CTE)

Simple CTE (PostgreSQL)

// Using raw query for CTE
const result = await userRepository.query(`
  WITH active_users AS (
    SELECT * FROM users WHERE "isActive" = true
  ),
  user_posts AS (
    SELECT "authorId", COUNT(*) as post_count
    FROM posts
    GROUP BY "authorId"
  )
  SELECT au.id, au.name, up.post_count
  FROM active_users au
  LEFT JOIN user_posts up ON up."authorId" = au.id
`);

CTE with QueryBuilder

// Build CTE parts separately
const activeUsersCTE = userRepository
  .createQueryBuilder('user')
  .where('user.isActive = true')
  .getQuery();

const userPostsCTE = postRepository
  .createQueryBuilder('post')
  .select('post.authorId', 'authorId')
  .addSelect('COUNT(*)', 'postCount')
  .groupBy('post.authorId')
  .getQuery();

// Combine in main query
const result = await userRepository
  .createQueryBuilder()
  .select('au.id', 'id')
  .addSelect('au.name', 'name')
  .addSelect('up.postCount', 'postCount')
  .from(`(${activeUsersCTE})`, 'au')
  .leftJoin(`(${userPostsCTE})`, 'up', 'up.authorId = au.id')
  .setParameters({ /* merge parameters */ })
  .getRawMany();

---

19.8 Recursive Queries

Tree Structure Query

// Get all descendants of a category (PostgreSQL)
const result = await categoryRepository.query(`
  WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 1 as level
    FROM categories
    WHERE id = $1

    UNION ALL

    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
  )
  SELECT * FROM category_tree ORDER BY level
`, [rootCategoryId]);

---

19.9 UNION Queries

UNION

const result = await dataSource
  .createQueryBuilder()
  .select('user.name', 'name')
  .addSelect('user.email', 'email')
  .addSelect("'user'", 'type')
  .from(User, 'user')
  .where('user.isActive = true')
  .union(
    dataSource
      .createQueryBuilder()
      .select('admin.name', 'name')
      .addSelect('admin.email', 'email')
      .addSelect("'admin'", 'type')
      .from(Admin, 'admin')
      .where('admin.isActive = true')
  )
  .getRawMany();

// SQL:
// SELECT user.name, user.email, 'user' as type FROM users user WHERE user.isActive = true
// UNION
// SELECT admin.name, admin.email, 'admin' as type FROM admins admin WHERE admin.isActive = true

UNION ALL

const result = await dataSource
  .createQueryBuilder()
  .select('name')
  .from(User, 'user')
  .unionAll(
    dataSource
      .createQueryBuilder()
      .select('name')
      .from(Admin, 'admin')
  )
  .getRawMany();

---

19.10 Advanced Query Patterns

Pagination with Subquery

// Efficient pagination using subquery
const page = 2;
const limit = 10;

const result = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('u.id')
      .from(User, 'u')
      .orderBy('u.createdAt', 'DESC')
      .limit(limit)
      .offset((page - 1) * limit)
      .getQuery();

    return `user.id IN ${subQuery}`;
  })
  .leftJoinAndSelect('user.posts', 'post')
  .orderBy('user.createdAt', 'DESC')
  .getMany();

Search with Ranking

const searchTerm = 'typescript';

const result = await postRepository
  .createQueryBuilder('post')
  .select([
    'post.id',
    'post.title',
    'post.content',
  ])
  .addSelect(qb => {
    return qb.subQuery()
      .select('similarity(post.title, :term)', 'titleRank')
      .setParameter('term', searchTerm);
  }, 'titleRank')
  .addSelect(qb => {
    return qb.subQuery()
      .select('similarity(post.content, :term)', 'contentRank')
      .setParameter('term', searchTerm);
  }, 'contentRank')
  .where('post.title ILIKE :term OR post.content ILIKE :term', {
    term: `%${searchTerm}%`,
  })
  .orderBy('titleRank', 'DESC')
  .addOrderBy('contentRank', 'DESC')
  .getRawMany();

---

19.11 Performance Considerations

                    Subquery Performance Tips
    +------------------------------------------------------------------+
    |                                                                   |
    |    1. Prefer JOINs over subqueries when possible                  |
    |       - JOINs are often optimized better                          |
    |       - Single query execution                                    |
    |                                                                   |
    |    2. Use EXISTS instead of IN for large datasets                 |
    |       - EXISTS stops at first match                               |
    |       - IN processes entire subquery                              |
    |                                                                   |
    |    3. Avoid correlated subqueries in SELECT                       |
    |       - Executes once per row                                     |
    |       - Use JOINs instead                                         |
    |                                                                   |
    |    4. Index columns used in subquery conditions                   |
    |       - Speeds up subquery execution                              |
    |                                                                   |
    |    5. Use LIMIT in subqueries when appropriate                    |
    |       - Reduces result set size                                   |
    |                                                                   |
    +------------------------------------------------------------------+

---

19.12 Summary

                    Subquery Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Location           | Method                                    |
    |    -------------------|------------------------------------------|
    |    SELECT             | .addSelect(qb => qb.subQuery()...)        |
    |    WHERE IN           | .where(qb => `id IN ${subQuery}`)         |
    |    WHERE EXISTS       | .where(qb => `EXISTS ${subQuery}`)        |
    |    FROM               | .from(qb => qb.subQuery()...)             |
    |    JOIN               | .leftJoin(qb => qb.subQuery()...)         |
    |                                                                   |
    +------------------------------------------------------------------+

---

Next Chapter

Chapter 20: Query Builder Best Practices

---

Last Updated: February 2026