Preserve unmatched rows
You need records from both tables, including rows with no counterpart.
Drizzle ORM · Module
.fullJoin
.fullJoin() combines two tables while retaining unmatched rows from both sides. Drizzle reflects this by making each joined table nullable in the inferred result.
How it works
Call fullJoin after from to combine two tables using an equality or other SQL condition. Because either table may lack a matching row, Drizzle infers each joined table as nullable in the result; selecting a nested object can keep that nullability at object level.
Syntax
db.select().from(leftTable).fullJoin(rightTable, joinCondition)Parameters
| Name | Type | Description |
|---|---|---|
table | Table | The table used as the right side of the full join. |
on | SQL condition | The SQL join predicate, commonly built with eq or another Drizzle SQL operator. |
Returns
A promise resolving to an array of selected rows; each joined table can be null when no matching row exists.
Key points
- The query builder exposes full joins alongside inner, left, right, and cross join APIs.
- Call fullJoin on the query after from, passing the joined table and an ON condition.
- Drizzle emits a SQL FULL JOIN with the supplied comparison as its ON clause.
- A nested select object is useful when a joined table contains many columns.
Common mistakes
- The full join makes each table-shaped result nullable, so handle missing rows before reading joined fields.
Example: Full join with Drizzle
const result = await db
.select()
.from(users)
.fullJoin(pets, eq(users.id, pets.ownerId));This query returns every user and every pet, matching rows where the user ID equals the pet owner ID; either side can be null when unmatched.
When to use it
Join on a table relationship
A query compares related columns across two tables.
Use nested selections
A joined table has many projected columns and nullable fields would be noisy.
Interview questions
QWhat does Drizzle's fullJoin method do?
fullJoin combines two tables with a SQL FULL JOIN and takes the table plus its join condition. The result includes matching rows and unmatched rows from both sides.
Follow-up: How does Drizzle type unmatched rows? Each joined table is represented as a nullable object, so application code must account for null on either side.
QWhat is the basic syntax for a full join?
Use fullJoin after from, passing the second table and a condition such as eq(users.id, pets.ownerId). Drizzle translates this into a FULL JOIN ... ON clause.
Follow-up: Can the projection be narrowed? Yes: pass a selection object to select and choose only the columns or expressions needed.
QHow does selection shape affect full-join nullability?
A full join returns nullable table objects because either side can be absent. Drizzle's nested select object syntax can make the whole selected object nullable instead of making every selected field nullable.
Follow-up: Why is that useful? It reduces repeated null checks when a joined table has many selected columns.
Related
selectfrominnerJoinleftJoinrightJoincrossJoineqsql