MSSQL join types supported
Drizzle ORM supports INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN for combining tables based on related columns.
mssql/joins
10 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
RIGHT JOIN syntax and result type
RIGHT JOIN is performed using `.rightJoin(table, condition)`. The resulting type makes the left table nullable. Example: `db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId))` returns `{ user: {...} | null; pets: {...} }[]`.
INNER JOIN syntax and result type
INNER JOIN is performed using `.innerJoin(table, condition)`. Neither table is nullable in the result. Example: `db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId))` returns `{ user: {...}; pets: {...} }[]`.
FULL JOIN syntax and result type
FULL JOIN is performed using `.fullJoin(table, condition)`. Both tables are nullable in the result. Example: `db.select().from(users).fullJoin(pets, eq(users.id, pets.ownerId))` returns `{ users: {...} | null; pets: {...} | null }[]`.
sql operator type inference in joins
When using the `sql` operator in partial selects with joins, explicitly specify the return type including null when needed. Use `sql<type | null>` to tell Drizzle the field can be null. For example, in a LEFT JOIN: `sql<string | null>\`upper(${pets.name})\`` ensures proper type inference.
Table aliases for self-joins
Use `alias(table, aliasName)` to create table aliases for performing self-joins. This allows a table to be joined to itself. Example: `const parent = alias(user, 'parent'); db.select().from(user).leftJoin(parent, eq(parent.id, user.parentId))`.
Aggregating join results with reduce
Drizzle ORM returns name-mapped results from the driver without changing structure. To transform many-to-one relational data, use JavaScript reduce to aggregate rows. Collect multiple rows per parent record into nested arrays or objects.
Many-to-one join pattern
For many-to-one relationships, LEFT JOIN from the parent table to the child table. Example: cities (parent) LEFT JOIN users (child). Multiple users can reference the same city.
Many-to-many join pattern
For many-to-many relationships, use a junction table with LEFT JOINs. Example: select from the junction table, then LEFT JOIN to both related tables on their respective foreign keys. This allows querying all participants in a group or all groups for a user.
MSSQL select query with left join example
Example query: `await db.select().from(countries).leftJoin(cities, eq(cities.countryId, countries.id)).where(eq(countries.id, 10))` - demonstrates selecting from countries table with a left join to cities and filtering by country id.