Preserve unmatched source rows
Return every user while optionally including a matching pet.
Drizzle ORM · Module
.leftJoin
leftJoin combines a source table with another table while preserving source rows that have no match. Drizzle models the joined table, or its projected fields, as nullable.
How it works
Call leftJoin with the table to include and an ON-condition such as eq(users.id, pets.ownerId). Drizzle emits a SQL LEFT JOIN and represents the joined table as nullable, preserving users even when no matching pet exists.
Syntax
db.select().from(source).leftJoin(table, on)Parameters
| Name | Type | Description |
|---|---|---|
source table | table expression | Table whose rows are preserved and used as the left side of the join. |
table | table expression | Table to join on the right side. |
on | SQL boolean expression | Condition translated to the SQL ON clause. |
Returns
A query result containing the source-table data and nullable joined-table data when no right-side match exists.
Key points
- leftJoin combines a source table with a related table in one query.
- The API keeps SQL-like query structure while checking the join expression with TypeScript.
- The documented call places the joined table and equality predicate directly after from.
- The generated SQL uses LEFT JOIN and places the equality expression in its ON clause.
- When selecting complete records, the joined table is typed as nullable.
- A projected column from the right-side table can be null when no matching row exists.
Common mistakes
- Because the right-side table may have no matching row, fields from the joined table can be null; handle that possibility in TypeScript.
Example: Left join users with pets
import { eq } from "drizzle-orm";
import { users, pets } from "./schema";
const result = await db
.select({
userId: users.id,
petId: pets.id,
})
.from(users)
.leftJoin(pets, eq(users.id, pets.ownerId));
Selects each user and the matching pet identifier; petId is nullable when no pet matches.
When to use it
Project a compact joined result
Fetch only selected identifiers or fields from both tables.
Type-safe relational querying
Build SQL-like, statically checked relationships between tables.
Interview questions
QWhat does leftJoin do in Drizzle, and how does it affect types?
leftJoin combines a source table with another table using an ON condition while retaining source rows that have no match. Drizzle reflects this in the result type by making the joined table nullable.
Follow-up: How would you select only the identifiers? Use a projection such as select({ userId: users.id, petId: pets.id }); the joined petId remains nullable.
QWhat arguments does leftJoin receive?
The method is used after from and takes the joined table plus a predicate, for example leftJoin(pets, eq(users.id, pets.ownerId)). Drizzle translates that structure to a SQL LEFT JOIN with the predicate as its ON clause.
Follow-up: What SQL shape should you expect? A LEFT JOIN between the source and joined tables followed by ON and the generated equality condition.
QWhen should you choose a left join over an inner join?
A left join is useful when every source row must remain in the result, even if the related table has no row. In the documented users-and-pets example, users without pets are still returned.
Follow-up: What should application code do with the missing relation? Treat the joined object, or its projected fields, as nullable before dereferencing it.
Related
innerJoinrightJoinfullJoincrossJoinleftJoinLateraleq