Joining tables
Combine rows from related tables in a single Drizzle query with inner and left joins.
You can read, filter, sort, page, and write, but every query you’ve written so far has touched exactly one table. Real features rarely stay inside one table. An invoice list shows the organization’s name, which lives in organizations. A line-item view shows the product, which lives somewhere else. A dashboard pulls numbers split across two tables and has to stitch them back together for a single row on screen.
Back when you built the schema, the foreign keys wired those tables together: an invoice’s organizationId points at an organization’s id. The foreign key is the wire. A join is how a query walks that wire and brings back columns from both sides in a single trip to the database.
One decision sits at the center of every join, and it isn’t about syntax. When you match a row on the left to a row on the right, what should happen to a left row that finds no partner, such as an invoice whose organization was deleted, or an invoice that hasn’t been assigned to anyone yet? You can keep it with blanks where the partner’s columns would go, or drop it from the result entirely. That single question, what happens to a row with no match, is the axis the four join shapes sit on, and it runs through this whole lesson. By the end you’ll write inner and left joins with both ways of shaping the result, read the nullability the type checker hands back when a match might be missing, and know when to flip a join around or reach for a different tool.
What a join does, and the four ways to handle a missing match
Section titled “What a join does, and the four ways to handle a missing match”Before any Drizzle syntax, it helps to settle the mental model, because the four join shapes are not four separate recipes to memorize. They are four answers to a single question.
A join takes two tables, a left table and a right table, and matches each left row against the right rows using a predicate you supply. That predicate is the on clause, and in practice it’s almost always foreign key equals primary key: invoices.organizationId equals organizations.id. When the predicate holds, the two rows are stitched into one combined row carrying columns from both.
The matching itself is mechanical. The only interesting question is what happens to a left row whose predicate matches nothing on the right, and, symmetrically, a right row that matches no left row. The four join shapes are exactly the four answers:
innerJoindrops the unmatched rows from both sides. Only matched pairs survive, so an invoice whose organization is missing simply isn’t in the result.leftJoinkeeps every left row no matter what. When a left row finds no match, it fills the right side’s columns withnull. Every invoice survives, and the ones with no organization carry blanks where the org columns would be.rightJoinis the mirror image: it keeps every right row andnulls out the left side when there’s no match. You’ll see it named, but in practice nobody reaches for it, because you can flip the two tables and write aleftJoininstead. “Keep every row of the table I started from” reads more naturally than the reverse.fullJoinkeeps both sides, and whichever side is missing getsnull. It’s genuinely rare, worth recognizing rather than memorizing.
The takeaway from this list is that the four are not equally common. innerJoin and leftJoin cover the overwhelming majority of joins you’ll write in a SaaS app. rightJoin and fullJoin are named here so you recognize them in someone else’s code, and then you can largely forget them.
The diagram below makes the absence behavior visible. On the left are the two source tables: three invoices A, B, C, where C belongs to no organization, and two organizations X, Y, where Y matches no invoice. Each of the four panels shows precisely which combined rows that shape returns, and a muted cell marks every spot a null lands. Read it as four answers to “what happens to C, and what happens to Y?”
Keep that picture in mind. Every code example from here on refers back to it: “the left row with no match” is the C row, and whether C shows up in your result depends entirely on which join you picked.
Your first join: matched pairs with innerJoin
Section titled “Your first join: matched pairs with innerJoin”Start with the simplest real join, written out end to end: every invoice paired with the organization it belongs to.
db.select({ invoice: invoices, org: organizations }) .from(invoices) .innerJoin(organizations, eq(invoices.organizationId, organizations.id));// → { invoice: Invoice; org: Organization }[].from(invoices) names the left table, the rows you start from. The object passed to db.select(...) is the selection: each key is a label, each value is a whole table, so the result groups that table’s full row under that name.
db.select({ invoice: invoices, org: organizations }) .from(invoices) .innerJoin(organizations, eq(invoices.organizationId, organizations.id));// → { invoice: Invoice; org: Organization }[]innerJoin names the right table to bring in. The result holds only invoices paired with their org, because inner means an invoice with no matching org gets dropped.
db.select({ invoice: invoices, org: organizations }) .from(invoices) .innerJoin(organizations, eq(invoices.organizationId, organizations.id));// → { invoice: Invoice; org: Organization }[]The second argument is the on predicate, foreign key equals primary key, which is the rule for deciding what matches what. It’s the same eq helper from the last lesson, and like every value you hand Drizzle, both sides are parameterized for you.
db.select({ invoice: invoices, org: organizations }) .from(invoices) .innerJoin(organizations, eq(invoices.organizationId, organizations.id));// → { invoice: Invoice; org: Organization }[]You never wrote that type. Drizzle built it from the two tables and the labels you chose: invoice is an Invoice, org is an Organization, and the whole thing is an array. The type follows the join, nested and typed, with no extra work from you.
To recap what you wrote: .from(invoices) is the left table, and .innerJoin(organizations, …) is the right table plus the rule for matching it. The selection object, { invoice: invoices, org: organizations }, assigns a label to each table’s row, so a single result row is { invoice: Invoice; org: Organization }, and the query returns an array of them. Those are the same Invoice and Organization types your schema already infers, so you didn’t restate a shape; you let the join build it. This is the principle from the last lesson, that the type follows the projection, now stretched across two tables.
One thing has to be right before you go further: the on predicate is not optional, and getting it wrong fails silently. A join’s job is to pair rows, and the predicate decides which pairs are real. Take it away, or write something trivially true like eq(invoices.id, invoices.id), and the database does the only thing left to it: it pairs every left row with every right row. That’s the cross product (the Cartesian product). With a thousand invoices and a hundred organizations, that’s a hundred thousand rows handed back to your app, with no error and no warning. It’s the join’s version of an UPDATE with no WHERE: legal SQL that quietly returns far more than you meant to ask for.
Two selection shapes: labeled rows vs. a flat projection
Section titled “Two selection shapes: labeled rows vs. a flat projection”You’ve seen the selection object group whole rows under labels. That’s one of two shapes, and choosing between them is where joins trip people up most often, so look at both side by side against the exact same join.
db.select({ invoice: invoices, org: organizations }) .from(invoices) .innerJoin(organizations, eq(invoices.organizationId, organizations.id));// → { invoice: Invoice; org: Organization }[]Result type: { invoice: Invoice; org: Organization }[]. Each table’s full row, grouped under its label. Reach for this when you want whole rows, or when both tables carry a column with the same name (id, createdAt) and you need the label to keep them apart.
db.select({ id: invoices.id, amountDue: invoices.amountDue, orgName: organizations.name,}) .from(invoices) .innerJoin(organizations, eq(invoices.organizationId, organizations.id));// → { id: string; amountDue: string; orgName: string }[]Result type: { id: string; amountDue: string; orgName: string }[]. Hand-pick a few columns from across the tables into one flat object. The result is a narrower type with no nesting, which a component can read straight through. Note amountDue stays string: money is numeric in the schema, and that comes back as a string, the same rule as before.
The labeled shape nests: row.invoice.amountDue, row.org.name. The flat shape doesn’t: row.amountDue, row.orgName. Same join, same rows; only the shape of each result row differs, and the inferred type tracks it exactly.
Which to pick is mostly about what the consumer wants. If a component renders a list and only needs three fields, the flat projection gives it precisely those three with no nesting to drill through. If you genuinely need the whole invoice and the whole org, to pass them on or to use many of their fields, the labeled shape hands you both intact.
There’s also a case where the labeled shape isn’t a preference but the only option. Both invoices and organizations have a column called id. A flat object can only have one key named id, so { id: invoices.id } quietly throws away the org’s id, and if you need both, a flat { id, id } isn’t even expressible. You have two ways around this: alias the keys yourself (invoiceId: invoices.id, orgId: organizations.id), or fall back to the labeled shape, where invoice.id and org.id live in different sub-objects and never collide. When column names clash, labels are how you keep both.
Left joins and the null the type checker hands you
Section titled “Left joins and the null the type checker hands you”Here’s a feature with a bug hiding in it. You want a list of every invoice alongside the name of the teammate it’s assigned to. The join looks obvious: invoices to users, matched on invoices.assignedToId.
db.select({ invoice: invoices, assignee: users }) .from(invoices) .innerJoin(users, eq(invoices.assignedToId, users.id));Ship that and a fraction of your invoices vanish from the list, and not the ones you’d guess. assignedToId is nullable: a brand-new invoice often has no assignee yet. An innerJoin keeps only rows whose predicate finds a partner, so every unassigned invoice gets silently dropped, exactly the ones a user most needs to see and act on. Where the cross product gave you too many rows, this gives you too few, and neither one raises an error.
This is what the absence axis was for. You don’t want to drop the unmatched invoices; you want to keep them and accept that their assignee is simply absent. That’s a leftJoin:
db.select({ invoice: invoices, assignee: users }) .from(invoices) .leftJoin(users, eq(invoices.assignedToId, users.id));// → { invoice: Invoice; assignee: User | null }[]Switching innerJoin to leftJoin keeps every invoice, and the type changes to match. Look at what assignee became: User | null. The left join told the type checker that a match might be missing, so the type checker now forces you to account for it. You cannot write row.assignee.name; TypeScript stops you, because row.assignee might be null. The type system isn’t getting in your way here. It’s raising the same “did this match?” question the opening diagram drew, at the point where you’d otherwise read a field off a row that has none.
One detail trips people up most often, so it’s worth being precise about. The nullability depends on which selection shape you used:
- With the labeled shape, the entire grouped object goes nullable:
assigneeisUser | null, the whole object, not aUserwhose every field happens to benull. Either there’s an assignee object or there’snull. - With a flat projection, each right-side column goes nullable on its own:
assigneeName: string | null. There’s no object to be null; the individual columns carry thenull.
Either way the fix downstream is the same: handle the null branch before you read the right side. Narrow with an if (row.assignee), reach for it with optional chaining (row.assignee?.name), or supply a fallback (row.assignee?.name ?? 'Unassigned'). The type is telling you the truth about the null. Skipping the narrowing to reach the field faster doesn’t avoid the problem; it just moves the missing-match failure from the database to a Cannot read properties of null error at runtime.
One more point here looks ahead to the next lesson. A flat join with nullable columns is the right tool when you want an irregular shape, a few specific columns drawn from across tables. But the most common reason you reach for a join is to load a row together with the rows related to it: an invoice with its line items, its tags, and its organization, as one nested object. For that tree shape, threading null through a flat join by hand is more work than it should be. The next lesson covers Drizzle’s relational query API, which returns the nested shape directly without any of this null-threading. So keep a raw leftJoin for the irregular and flat cases, and let the relational API handle the “row plus its related rows” tree.
Now write one yourself. The exercise below seeds five invoices, two of them unassigned. Your job is to return every invoice with its assignee’s name, including the unassigned ones. There’s a tempting wrong answer, innerJoin, that looks right until you notice it’s two rows short.
Return every invoice with the name of its assignee — including invoices that have no assignee yet. Project invoiceId, amountDue, and assigneeName. Two of the five invoices are unassigned, so an innerJoin will silently come back two rows short.
View schema & seed rows
export const users = pgTable('users', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
export const invoices = pgTable('invoices', {
id: integer('id').primaryKey(),
organizationId: integer('organization_id').notNull(),
assignedToId: integer('assigned_to_id').references(() => users.id),
amountDue: numeric('amount_due').notNull(),
status: text('status').notNull(),
}); INSERT INTO users (id, name) VALUES (1, 'Mara Liu'), (2, 'Devin Cole'); INSERT INTO invoices (id, organization_id, assigned_to_id, amount_due, status) VALUES (1, 1, 1, '120.00', 'sent'), (2, 1, 2, '450.00', 'paid'), (3, 1, NULL, '75.00', 'draft'), (4, 1, 1, '999.00', 'sent'), (5, 1, NULL, '30.00', 'draft');
- Query returns the 5 expected rows (any order)
Joining a table to itself with alias
Section titled “Joining a table to itself with alias”Sometimes the two sides of a join are the same table. A comment and the comment it replies to both live in comments; a category and its parent category both live in categories. Your schema already has this shape: a comment’s parentId points back at another comment’s id.
The problem is naming. If you mention comments twice in one query, the database can’t tell which one a column like comments.id refers to. So you give the second appearance its own name with alias from drizzle-orm:
import { alias } from 'drizzle-orm';
const parent = alias(comments, 'parent');
db.select({ reply: comments, parent }) .from(comments) .leftJoin(parent, eq(comments.parentId, parent.id));alias(comments, 'parent') is a second handle on the same table under a distinct SQL name, and from there it joins exactly like any other table. Note it’s a leftJoin, not an inner one: a top-level comment has no parent, so the parent side comes back null, and the same nullability handling from the last section applies. The aliasing is the only genuinely new idea here; everything else you already know.
Many-to-many: two joins through the junction table
Section titled “Many-to-many: two joins through the junction table”Here is a multi-table query you’ll write constantly: walking a many-to-many relationship. An invoice has many tags, and a tag applies to many invoices. That relationship doesn’t live on either table. It lives in the junction table from your schema, invoice_tags, where every row is just a pair: one invoiceId, one tagId.
To get an invoice’s tags, you hop across that junction in two joins. Start at invoices, join into invoice_tags to find the pairs for this invoice, then join from there into tags to turn each pair’s tagId into a real tag row.
db.select({ invoiceId: invoices.id, tagName: tags.name }) .from(invoices) .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id)) .innerJoin(tags, eq(invoiceTags.tagId, tags.id)) .where(eq(invoices.id, id));// → { invoiceId: string; tagName: string }[] — one row per tagStart at invoices, the left table. We want just two columns out: the invoice’s id and each tag’s name.
db.select({ invoiceId: invoices.id, tagName: tags.name }) .from(invoices) .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id)) .innerJoin(tags, eq(invoiceTags.tagId, tags.id)) .where(eq(invoices.id, id));// → { invoiceId: string; tagName: string }[] — one row per tagHop one: into the junction. Match invoiceTags.invoiceId to invoices.id, which pulls in every junction row belonging to this invoice, one per tag it carries.
db.select({ invoiceId: invoices.id, tagName: tags.name }) .from(invoices) .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id)) .innerJoin(tags, eq(invoiceTags.tagId, tags.id)) .where(eq(invoices.id, id));// → { invoiceId: string; tagName: string }[] — one row per tagHop two: from the junction into tags. Match invoiceTags.tagId to tags.id, turning each junction pair into the actual tag row, so tags.name is now reachable.
db.select({ invoiceId: invoices.id, tagName: tags.name }) .from(invoices) .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id)) .innerJoin(tags, eq(invoiceTags.tagId, tags.id)) .where(eq(invoices.id, id));// → { invoiceId: string; tagName: string }[] — one row per tagScope the query to one invoice with where, passing the id you’re looking up. The result shape is the catch: one row per tag, so an invoice with three tags comes back as three rows, with the invoiceId repeated on each.
That last point deserves a closer look. This query returns one row per tag: an invoice with three tags comes back as three rows, each repeating the same invoiceId next to a different tagName. That’s correct, since it’s what a flat join across a one-to-many produces, but it’s almost never the shape your UI wants. A UI wants one invoice object with a tags array on it, and reshaping these repeated rows into that by hand is fiddly and easy to get wrong.
This is the second time the same gap has come up: raw joins give you flat rows, and a tree is what you actually want. The next lesson’s relational API walks this exact junction for you. Ask for with: { tags: true } and it returns one invoice object carrying a real tags array, with no double join and no row multiplication to undo. For now, the explicit double join is the right tool when you do want the flat, per-tag shape: a CSV export, for instance, or the groundwork for counting tags per invoice, which is a couple of lessons away.
When to reach for a join, and when not
Section titled “When to reach for a join, and when not”Step back from the syntax for a moment, because there’s a judgment call you’ll make on every join-shaped read. Both the raw db.select().join() you’ve been writing and the relational query API you’ll meet next return fully Drizzle-typed results. So the decision between them is never about what’s possible, since both can do it. It’s about the shape of what you’re reading.
Reach for the relational query API (next lesson) when the read is a tree: a row together with its related rows as nested objects and arrays, like an invoice with its line items and its tags and its organization. It returns that nested shape directly, it’s safe from the performance trap of firing one query per related row, and you never thread null or de-duplicate repeated rows by hand. For the everyday “load this thing and the things hanging off it,” that’s the default.
Reach for a hand-written join (this lesson) when the read isn’t a clean tree:
- The projection is an irregular flat shape: a handful of columns gathered from three different tables into one flat row, like
invoiceId, orgName, assigneeNamefor a CSV export. - The predicate spans several joined tables in a way that doesn’t map cleanly onto “this row and its children.”
- An aggregate enters the picture, a count or a sum computed across joined rows. That’s a couple of lessons away, and it’s a clear signal to drop down to an explicit join.
To put it plainly: in this course, most join-heavy reads go through the relational API, and raw db.select(...).join(...) is what you reach for on the shapes it doesn’t cover. The next lesson is devoted to that relational API, and a bit further on, aggregations over joins get their own lesson too. Once your joins are correct, the chapter after this one makes them fast. The foreign-key columns you’re matching on need indexes; without them, every join you just learned scans the whole table to find its matches. Correctness comes first here, and speed comes later.
Try sorting a few real read requirements into the two buckets. For each one, decide whether its natural shape is a tree (reach for the relational API) or a flat/irregular/aggregate shape (reach for a hand-written join).
Sort each read requirement by its natural shape: a nested tree the relational API returns directly, or a flat, irregular, or aggregate shape that wants a hand-written join. Drag each item into the bucket it belongs to, then press Check.
invoice_id, org_name, assignee_name for a CSV exportKeep these close
Section titled “Keep these close”The Drizzle docs cover every join method, the aliasing helper, and both selection shapes in one place. It’s worth a skim now that you’ve written the shapes by hand, and worth a bookmark for the next time rightJoin or fullJoin shows up and you want to confirm what it does.
The four join methods, aliasing for self-joins, and labeled vs. partial selections.
The underlying SQL semantics of inner, left, right, and full joins.
External resources
Section titled “External resources”Here are two more ways to cement the missing-match axis from the top of the lesson, one to play with and one to read. Both lean on the Venn-diagram picture. Treat that picture as a way to recognize a join shape in someone else’s code, and keep the absence axis as your working model for choosing one.