Choose a join or relational query

A join and a relational query can answer the same question. The clearer result shape should drive the choice.

An explicit join keeps selected columns and SQL structure visible. The relational API is convenient when the application wants nested objects. Neither approach removes the need to limit rows, filter ownership, or inspect performance.

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

For an export with two columns, the explicit join is easy to read. For a user page containing a user object and a notes array, db.query.users.findFirst({ with: { notes: true } }) may match the application shape better.

Do not fetch every column and discard most of them later. The query should express the data boundary, especially when rows contain private or large fields.

Implement the same “newest notes with author” screen using a join and a relational query. Compare generated SQL, result shape, selected columns, and query plan before choosing one.