Choose a join or relational query

Drizzle gives you two ways to fetch related data: an explicit join, or the relational query API we enabled in the last lesson. Both answer “notes with their authors”. They return different shapes, and the shape your code needs should drive the choice.

The explicit join

A join reads like SQL and returns flat rows:

import { eq } from 'drizzle-orm'

const rows = await db
  .select({
    title: notes.title,
    authorName: users.name,
  })
  .from(notes)
  .innerJoin(users, eq(notes.authorId, users.id))

[
  { title: 'First note', authorName: 'Flavio' },
  { title: 'Second note', authorName: 'Flavio' }
]

The generated SQL is the one you’d write by hand:

select "notes"."title", "users"."name"
from "notes"
inner join "users" on "notes"."author_id" = "users"."id"

You pick the exact columns. You see the join condition. For a CSV export or a table with two columns, this is the clearest option.

The relational query

The relational API returns nested objects:

const user = await db.query.users.findFirst({
  with: { notes: true },
})

{
  id: 1,
  email: '[email protected]',
  name: 'Flavio',
  notes: [
    { id: 1, authorId: 1, title: 'First note', body: '', createdAt: '2026-09-07 21:09:14', archivedAt: null },
    { id: 2, authorId: 1, title: 'Second note', body: '', createdAt: '2026-09-07 21:09:14', archivedAt: null }
  ]
}

A user object with a notes array inside. For a profile page that renders exactly that, you’d otherwise write the join and then regroup rows in JavaScript. The relational API does it for you, in one SQL statement that builds the nested array with a JSON subquery. Call .toSQL() on it if you’re curious; it’s longer than a join, but it’s still one round trip.

Neither one excuses you from the rules

Both approaches need a filter, a limit, and an ownership check. findFirst above returned the first user in the table, with every one of their notes. In a real app you’d add where: { id: userId } and limit the notes.

And both let you fetch too much. The relational query above pulled body for every note, which the page may not show. Narrow it:

const user = await db.query.users.findFirst({
  where: { id: userId },
  columns: { id: true, name: true },
  with: {
    notes: {
      columns: { id: true, title: true },
      orderBy: { createdAt: 'desc' },
      limit: 20,
    },
  },
})

Now the query expresses the data boundary. Nothing private or heavy crosses into the application by accident.

How I choose

Flat rows for a flat output: joins. Nested objects for a nested screen: relational query. When the query gets complex, with aggregates or several conditions across tables, I go back to the join, because I can read the SQL it produces.

Build the “newest notes with author name” screen both ways. Compare toSQL() output, the result type, and the columns each one selects. Then keep the one whose shape matches what the page renders, and delete the other.