Drizzle ORM join types supported
Drizzle ORM supports INNER JOIN, INNER JOIN LATERAL, LEFT JOIN, LEFT JOIN LATERAL, RIGHT JOIN, and CROSS JOIN with optional LATERAL modifier.
joins & relationships
16 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
INNER JOIN syntax and return type
INNER JOIN in Drizzle ORM uses the syntax `db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId))`. The return type includes both tables' fields as non-null since INNER JOIN only returns rows where both tables have matching values.
RIGHT JOIN syntax and return type
RIGHT JOIN in Drizzle ORM uses the syntax `db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId))`. The return type makes the left table (users) nullable since RIGHT JOIN returns all rows from the right table (pets) and only matching rows from the left table.
CROSS JOIN syntax and return type
CROSS JOIN in Drizzle ORM uses the syntax `db.select().from(users).crossJoin(pets)`. The return type includes both tables' fields as non-null, producing a Cartesian product of the two tables.
LEFT JOIN LATERAL syntax
LEFT JOIN LATERAL in Drizzle ORM uses the syntax `db.select().from(users).leftJoinLateral(subquery, sql`true`)`. The subquery is aliased and can reference columns from the outer query. The result type makes the subquery results nullable.
INNER JOIN LATERAL syntax
INNER JOIN LATERAL in Drizzle ORM uses the syntax `db.select().from(users).innerJoinLateral(subquery, sql`true`)`. The subquery is aliased and can reference columns from the outer query. The result type makes the subquery results non-null.
CROSS JOIN LATERAL syntax
CROSS JOIN LATERAL in Drizzle ORM uses the syntax `db.select().from(users).crossJoinLateral(subquery)`. No ON clause is needed.
Partial select in joins
Drizzle ORM supports partial select in joins using `.select({ userId: users.id, petId: pets.id })`. The return type is automatically inferred based on the selected fields. When using LEFT JOIN, fields from the joined table become nullable.
SQL operators with type annotations in joins
When using the `sql` operator in partial select with joins, use `sql<type | null>` to explicitly specify the return type for proper type inference. For example, `sql<string | null>`upper(${pets.name})`` when the field comes from a LEFT JOINed table.
Nested select object syntax in joins
Drizzle ORM supports nested select objects in joins. Instead of making all table fields nullable, you can nest fields from a joined table in a single object, which makes the entire object nullable. For example, `{ userId: users.id, pet: { id: pets.id, name: pets.name } }` results in type `{ userId: number; pet: { id: number; name: string; } | null; }`.
Table aliases for selfjoins
Drizzle ORM supports table aliases using `alias(user, 'parent')` to perform selfjoins. This creates an aliased reference to the same table with a different name, allowing the table to be joined to itself.
Aggregating many-to-one join results
To aggregate many-to-one join results in Drizzle ORM, use `Array.reduce()` to map flat query results into a nested structure. For example, reduce rows with user and pet data into a record keyed by user ID with nested pet arrays.
Many-to-one join example
A many-to-one relationship is implemented by having the 'many' table reference the 'one' table via a foreign key. For example, a users table with a cityId foreign key references the cities table. Query it with `db.select().from(cities).leftJoin(users, eq(cities.id, users.cityId))`.
Many-to-many join example
A many-to-many relationship uses a junction table with foreign keys to both related tables. For example, usersToChatGroups table with userId and groupId foreign keys. Query it by joining the junction table to both related tables: `db.select().from(usersToChatGroups).leftJoin(users, eq(usersToChatGroups.userId, users.id)).leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id))`.
Difference between INNER JOIN and LEFT JOIN
INNER JOIN returns only rows where both tables have matching values, making all fields non-nullable. LEFT JOIN returns all rows from the left table and matching rows from the right table, making the right table's fields nullable in the result type.
Drizzle ORM join type inference
Drizzle ORM automatically infers result types based on join type. INNER JOIN produces non-nullable joined table fields, while LEFT/RIGHT JOIN produces nullable fields for the respective tables based on which side of the join may have no matches.