Joining tables

You can read, filter, sort, page, and write, but every query so far has touched one table. Real features rarely stay there: an invoice list shows the organization’s name, which lives in organizations.

The foreign keys in your schema already connect those tables: an invoice’s organizationId points at an organization’s id. A join is how one query follows that link and returns columns from both tables 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 left row to a right row, what happens to a left row that finds no partner, such as an invoice whose organization was deleted? You can keep it, with blanks where the partner’s columns would go, or drop it. That question, what happens to a row with no match, is the axis the four join shapes sit on, and it runs through the whole lesson.

What a join does, and four ways to handle a missing match

A join takes a left table and a right table and matches each left row against the right rows using a predicate, the on clause. That predicate is almost always foreign key equals primary key: invoices.organizationId equals organizations.id. When it holds, the two rows merge into one row carrying columns from both.

The matching is mechanical; the four shapes differ only in what they do with a left row that matches nothing on the right, and, symmetrically, a right row that matches no left row:

• innerJoin drops unmatched rows from both sides. Only matched pairs survive, so an invoice with no organization is absent from the result.
• leftJoin keeps every left row and fills the right side’s columns with null when there’s no match. Every invoice survives; the ones with no organization carry blanks where the org columns would be.
• rightJoin is the mirror: it keeps every right row and nulls the left side. Nobody reaches for it, since flipping the two tables and writing a leftJoin says the same thing more naturally.
• fullJoin keeps both sides, nulling whichever is missing. Rare; recognize it rather than memorize it.

innerJoin and leftJoin cover almost every join you’ll write; the other two are here so you recognize them in someone else’s code.

The diagram below makes the absence behavior visible. The source tables hold three invoices A, B, C, where C belongs to no organization, and two organizations X, Y, where Y matches no invoice. Each panel shows which combined rows that shape returns, with a muted cell wherever a null lands.

invoices

A org X

B org X

C org null

organizations

X used by A, B

Y used by none

matched value

null (no match)

innerJoin

invoice org

A X

B X

C and Y dropped

leftJoin

invoice org

A X

B X

C null

C kept, org is null

rightJoin

invoice org

A X

B X

null Y

Y kept, invoice is null

fullJoin

invoice org

A X

B X

C null

null Y

both kept

Inner and left are the daily tools; right and full are here so you recognize them. Every shape is one answer to: what happens to the row with no match?

Every example from here on refers back to this: “the left row with no match” is C, and whether C appears in your result depends entirely on the join you picked.

Watch video

Anton Putra animates all four shapes plus the cross join in 10 minutes, the same missing-match axis in raw SQL.

Your first join: matched pairs with innerJoin

Start with the simplest real join: 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 you pass to db.select(...) is the selection: each key is a label, each value 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. Inner means an invoice with no matching org is dropped, so the result holds only invoices paired with their org.

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, the rule for what matches what: foreign key equals primary key. Same eq helper as the last lesson, both sides 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 your labels: invoice is an Invoice, org is an Organization, in an array. The type follows the projection, the principle from the last lesson now stretched across two tables.

← Previous 1 / 1 Next →

One thing has to be right before you go further: the on predicate is not optional, and getting it wrong fails silently. Take it away, or write something trivially true like eq(invoices.id, invoices.id), and the database pairs every left row with every right row, the cross product . A thousand invoices and a hundred organizations come back as a hundred thousand rows, with no error and no warning.

Danger

Every join needs a real on predicate tying the two tables together, normally foreign key equals primary key. A missing or always-true predicate produces a cross product: N×M rows, silently, with no compile error. In production that lands at runtime under load as a performance incident. Treat a join’s on clause with the same suspicion you give a mutation’s where.

Two selection shapes: labeled rows vs. a flat projection

The selection object that groups whole rows under labels is one of two shapes a join result can take. The join itself is the same either way.

Labeled rows

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 share a column name (id, createdAt) and you need the label to keep them apart.

Flat projection

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 columns from across the tables into one flat object a component can read straight through. amountDue stays string: money is numeric in the schema, which comes back as a string.

The labeled shape nests: row.invoice.amountDue, row.org.name. The flat shape doesn’t: row.amountDue, row.orgName. Same rows, different shape, and the inferred type tracks it exactly.

Pick by what the consumer needs. A component rendering a list of three fields wants the flat projection, with nothing to drill through; code that passes a whole invoice and a whole org onward wants the labeled shape, which hands back both intact.

Sometimes the labeled shape is the only option. Both invoices and organizations have an id, and a flat object can hold only one key named id, so { id: invoices.id } silently drops the org’s. To keep both, either alias the keys (invoiceId: invoices.id, orgId: organizations.id) or use the labeled shape, where invoice.id and org.id sit in separate sub-objects.

Left joins and the null you have to handle

You want every invoice alongside the name of the teammate it’s assigned to. The obvious join matches invoices to users 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. assignedToId is nullable, and a brand-new invoice often has no assignee yet. An innerJoin keeps only rows whose predicate finds a partner, so every unassigned invoice is silently dropped, exactly the ones a user most needs to act on. No error is raised; you just get too few rows.

You want to keep the unmatched invoices and accept that their assignee is 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 }[]

A leftJoin keeps every invoice, and assignee becomes User | null. You cannot write row.assignee.name: TypeScript stops you because row.assignee might be null, raising the “did this match?” question exactly where you’d otherwise read a field off a row that has none.

Which side goes nullable depends on the selection shape:

• With the labeled shape, the entire grouped object goes nullable: assignee is User | null, either an assignee object or null.
• With a flat projection, each right-side column goes nullable on its own: assigneeName: string | null. There’s no object to be null; the columns carry it.

Either way, handle the null before reading the right side: narrow with if (row.assignee), chain with row.assignee?.name, or supply a fallback with row.assignee?.name ?? 'Unassigned'. Skipping the narrowing doesn’t avoid the problem; it moves the missing-match failure from the database to a Cannot read properties of null error at runtime.

Keep a raw leftJoin for irregular, flat shapes. To load a row together with its related rows as one nested object, the next lesson’s relational query API returns that tree directly without any null-threading.

The exercise seeds five invoices, two of them unassigned, and asks for every invoice with its assignee’s name, including the unassigned ones. The tempting innerJoin 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

Schema (Drizzle)

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(),
});

Seed rows (SQL)

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');

Run query Feedback Reset

Joining a table to itself with alias

Sometimes both sides of a join are the same table, a self-join . Your schema already has this shape: a comment’s parentId points back at another comment’s id, so a reply and the comment it replies to both live in comments.

The problem is naming. Mention comments twice in one query and the database can’t tell which one comments.id refers to. 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));

Now parent is a second handle on the table under a distinct SQL name, and it joins like any other. Use leftJoin, not an inner one: a top-level comment has no parent, so its parent side comes back null, with the same nullability handling as the last section.

Many-to-many: two joins through the junction table

An invoice has many tags, and a tag applies to many invoices. That relationship lives on neither table. It lives in the junction table from your schema, invoice_tags, where every row pairs one invoiceId with one tagId.

To get an invoice’s tags, hop across that junction in two joins: start at invoices, join into invoice_tags to find this invoice’s pairs, then join into tags to turn each pair’s tagId into a 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 tag

Start at invoices, the left table. We want 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 tag

First hop, into the junction. Match invoiceTags.invoiceId to invoices.id, pulling every junction row for this invoice, one per tag.

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 tag

Second hop, from the junction into tags. Match invoiceTags.tagId to tags.id, turning each pair into its 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 tag

Scope to one invoice with where. The catch is the result shape: one row per tag, so a three-tag invoice comes back as three rows with invoiceId repeated.

← Previous 1 / 1 Next →

This flat, per-tag shape is rarely what a UI wants. A UI wants one invoice object with a tags array, and reshaping the repeated rows into that by hand is fiddly and error-prone. The next lesson’s relational API does it for you: ask for with: { tags: true } and you get one invoice carrying a real tags array. Reach for the explicit double join when you do want flat rows, such as a CSV export.

Choosing between a join and the relational API

Both raw db.select().join() and the relational query API you’ll meet next return fully Drizzle-typed results, so the choice is never about what’s possible. 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 with its related rows nested as objects and arrays, like an invoice with its line items, tags, and organization. It returns that nested shape directly, without one query per related row or hand-threading null and de-duplicating repeated rows. For “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 few columns pulled from three tables into one row, like invoiceId, orgName, assigneeName for a CSV export.
• The predicate spans several joined tables in a way that doesn’t map onto “this row and its children.”
• An aggregate is involved, a count or sum across joined rows, covered in a later lesson.

One thing waits for the next chapter: the foreign-key columns you join on need indexes, or every join scans the whole table to find its matches. Get the joins correct first; speed comes next.

Sort each read below by its natural shape: a tree (relational API) or a flat, irregular, or aggregate shape (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.

Relational API A row plus its related rows, as a nested tree

Hand-written join A flat, irregular, or aggregate shape

An invoice with its line items as a nested array

A comment thread with each reply’s author attached

An organization with all of its invoices nested under it

A flat list of invoice_id, org_name, assignee_name for a CSV export

Total revenue summed per organization

The number of tags on each invoice

Check Reset

Keep these close

The Drizzle docs cover every join method, the aliasing helper, and both selection shapes in one place. Bookmark it for the next time rightJoin or fullJoin shows up.

Drizzle — Joins

orm.drizzle.team

The four join methods, aliasing for self-joins, and labeled vs. partial selections.

PostgreSQL — Joined Tables

postgresql.org

The underlying SQL semantics of inner, left, right, and full joins.

External resources

Two more takes on the missing-match axis, both built on the Venn-diagram picture: one to play with, one to read.

SQL Joins Visualizer

sql-joins.leopard.in.ua

Pick a join, watch the Venn diagram and the generated SQL update together: the absence axis, made interactive.

A Visual Explanation of SQL Joins

blog.codinghorror.com

Jeff Atwood's canonical post pairing each join with a diagram and the exact rows it returns.