Joins

Drizzle ORM provides a comprehensive set of join operations to combine data from multiple tables with full type safety.

Inner Join

Retrieve rows that have matching values in both tables:

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

// Get users with their posts
const result = await db.select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId));

// Result structure:
// [
//   { users: {...}, posts: {...} },
//   { users: {...}, posts: {...} },
// ]

Inner joins only return rows where the join condition is met in both tables. Users without posts won’t appear in the results.

Left Join

Retrieve all rows from the left table, with matched rows from the right table:

// Get all users and their posts (if they have any)
const result = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// Users without posts will have posts: null
// [
//   { users: {...}, posts: {...} },
//   { users: {...}, posts: null },  // User with no posts
// ]

Left joins return all rows from the left table. If no match is found in the right table, the right side fields will be null.

Right Join

Retrieve all rows from the right table, with matched rows from the left table:

// Get all posts with their authors (even if author is missing)
const result = await db.select()
  .from(users)
  .rightJoin(posts, eq(users.id, posts.userId));

// Posts without users will have users: null
// [
//   { users: {...}, posts: {...} },
//   { users: null, posts: {...} },  // Post with deleted user
// ]

Full Join

Retrieve all rows from both tables:

// Get all users and all posts, matched where possible
const result = await db.select()
  .from(users)
  .fullJoin(posts, eq(users.id, posts.userId));

// Both sides can be null
// [
//   { users: {...}, posts: {...} },  // Matched
//   { users: {...}, posts: null },   // User without posts
//   { users: null, posts: {...} },   // Post without user
// ]

Full join is PostgreSQL-specific. It’s not available in MySQL or SQLite.

Cross Join

Create a Cartesian product of two tables:

// Every user paired with every post
const result = await db.select()
  .from(users)
  .crossJoin(posts);

// No join condition needed
// If 5 users and 10 posts, returns 50 rows

Multiple Joins

Join more than two tables:

import { users, posts, comments } from './schema';

// Get users with their posts and comments
const result = await db.select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId))
  .innerJoin(comments, eq(posts.id, comments.postId));

// Result:
// [
//   {
//     users: {...},
//     posts: {...},
//     comments: {...}
//   }
// ]

Join with Partial Select

Select specific columns from joined tables:

const result = await db.select({
  userId: users.id,
  userName: users.name,
  postId: posts.id,
  postTitle: posts.title,
})
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// Result is flattened:
// [
//   { userId: 1, userName: 'John', postId: 10, postTitle: 'Hello' },
//   { userId: 2, userName: 'Jane', postId: null, postTitle: null },
// ]

Filtering Joined Data

Apply where conditions to joined tables:

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

// Only users older than 18 with their posts
const result = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .where(gt(users.age, 18));

Complex Join Conditions

Use multiple conditions in join clauses:

import { and, eq, gt } from 'drizzle-orm';

// Join with multiple conditions
const result = await db.select()
  .from(users)
  .leftJoin(posts, and(
    eq(users.id, posts.userId),
    eq(posts.published, true),
    gt(posts.views, 100)
  ));

Self Joins

Join a table to itself:

import { alias } from 'drizzle-orm/pg-core';

// Create an alias for self-join
const managers = alias(users, 'managers');

// Get employees with their managers
const result = await db.select({
  employeeName: users.name,
  managerName: managers.name,
})
  .from(users)
  .leftJoin(managers, eq(users.managerId, managers.id));

Lateral Joins (PostgreSQL)

Use lateral joins for correlated subqueries:

import { sql } from 'drizzle-orm';

// Get each user with their 3 most recent posts
const latestPosts = db.select()
  .from(posts)
  .where(eq(posts.userId, users.id))
  .orderBy(desc(posts.createdAt))
  .limit(3)
  .as('latest_posts');

const result = await db.select()
  .from(users)
  .leftJoinLateral(latestPosts, sql`true`);

Join with Aggregations

Combine joins with aggregate functions:

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

// Count posts per user
const result = await db.select({
  userId: users.id,
  userName: users.name,
  postCount: count(posts.id),
})
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .groupBy(users.id, users.name);

Join with Subqueries

Join to subquery results:

import { sql } from 'drizzle-orm';

// Create a subquery
const recentPosts = db.select({
  id: posts.id,
  userId: posts.userId,
  title: posts.title,
})
  .from(posts)
  .where(gt(posts.createdAt, sql`now() - interval '7 days'`))
  .as('recent_posts');

// Join with the subquery
const result = await db.select()
  .from(users)
  .innerJoin(recentPosts, eq(users.id, recentPosts.userId));

Many-to-Many Joins

Join through a junction table:

import { students, courses, enrollments } from './schema';

// Get students with their enrolled courses
const result = await db.select({
  studentName: students.name,
  courseName: courses.name,
})
  .from(students)
  .innerJoin(enrollments, eq(students.id, enrollments.studentId))
  .innerJoin(courses, eq(enrollments.courseId, courses.id));

Join Types Comparison

Inner Join

Returns only matching rows from both tables. Use when you need data that exists in both tables.

Left Join

Returns all rows from left table, matched rows from right. Use to include all primary records.

Right Join

Returns all rows from right table, matched rows from left. Less common, often swapped for left join.

Full Join

Returns all rows from both tables. Use when you need complete data from both sides.

Cross Join

Cartesian product of both tables. Use for combinations or mathematical operations.

Type Safety with Joins

Drizzle provides full type safety for joined queries:

const result = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// TypeScript knows the structure:
// result: Array<{
//   users: User;
//   posts: Post | null;
// }>

result.forEach(row => {
  console.log(row.users.name);  // Always available
  console.log(row.posts?.title); // Nullable with left join
});

Performance Tips

Index Join Columns

Ensure foreign key columns used in joins are indexed for optimal performance.

Select Only Needed Columns

Use partial selects to reduce data transfer when joining large tables.

Filter Early

Apply WHERE conditions to reduce rows before joining when possible.

Avoid N+1 Queries

Use joins instead of multiple separate queries to fetch related data.

Common Join Patterns

User Posts
Post Comments
Categories
Hierarchical

// Get users with their posts
const usersWithPosts = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// Get posts with comment counts
const postsWithComments = await db.select({
  postId: posts.id,
  title: posts.title,
  commentCount: count(comments.id),
})
  .from(posts)
  .leftJoin(comments, eq(posts.id, comments.postId))
  .groupBy(posts.id, posts.title);

// Get products with category names
const productsWithCategory = await db.select({
  productName: products.name,
  categoryName: categories.name,
})
  .from(products)
  .innerJoin(categories, eq(products.categoryId, categories.id));

// Self-join for hierarchical data
const children = alias(categories, 'children');

const hierarchy = await db.select({
  parentName: categories.name,
  childName: children.name,
})
  .from(categories)
  .leftJoin(children, eq(categories.id, children.parentId));

Next Steps

Aggregations

Learn about aggregate functions with joins

Subqueries

Use subqueries in your joins

Select Queries

Master the select query builder

Performance

Optimize your join queries

Get Started

Core Concepts

Database Drivers

Query Builders

Schema Definition

Advanced Features

Ecosystem

Guides

Inner Join

Left Join

Right Join

Full Join

Cross Join

Multiple Joins

Join with Partial Select

Filtering Joined Data

Complex Join Conditions

Self Joins

Lateral Joins (PostgreSQL)

Join with Aggregations

Join with Subqueries

Many-to-Many Joins

Join Types Comparison

Inner Join

Left Join

Right Join

Full Join

Cross Join

Type Safety with Joins

Performance Tips

Common Join Patterns

Next Steps

Aggregations

Subqueries

Select Queries

Performance

Build docs developers (and LLMs) love

Get Started

Core Concepts

Database Drivers

Query Builders

Schema Definition

Advanced Features

Ecosystem

Guides

​Inner Join

​Left Join

​Right Join

​Full Join

​Cross Join

​Multiple Joins

​Join with Partial Select

​Filtering Joined Data

​Complex Join Conditions

​Self Joins

​Lateral Joins (PostgreSQL)

​Join with Aggregations

​Join with Subqueries

​Many-to-Many Joins

​Join Types Comparison

Inner Join

Left Join

Right Join

Full Join

Cross Join

​Type Safety with Joins

​Performance Tips

​Common Join Patterns

​Next Steps

Aggregations

Subqueries

Select Queries

Performance

Build docs developers (and LLMs) love

Inner Join

Left Join

Right Join

Full Join

Cross Join

Multiple Joins

Join with Partial Select

Filtering Joined Data

Complex Join Conditions

Self Joins

Lateral Joins (PostgreSQL)

Join with Aggregations

Join with Subqueries

Many-to-Many Joins

Join Types Comparison

Type Safety with Joins

Performance Tips

Common Join Patterns

Next Steps