Basics

Override: Joins

Join helpers cover standard joins plus Postgres-specific lateral and natural variants.

Join Basics

Use innerJoin, leftJoin, rightJoin,fullJoin, or crossJoin.

const query = q
  .select(q.c("orders.id"), q.c("users.email"))
  .from(q.t("orders"))
  .innerJoin(
    q.t("users"),
    q.eq(q.c("orders.user_id"), q.c("users.id")),
  )

const query = q
  .select(q.c("orders.id"), q.c("users.email"))
  .from(q.t("orders"))
  .innerJoin(
    q.t("users"),
    q.eq(q.c("orders.user_id"), q.c("users.id")),
  )

Lateral Join

Lateral joins allow correlated subqueries usingleftJoinLateral and innerJoinLateral.

const query = q
  .select(q.c("users.id"), q.c("events.id"))
  .from(q.t("users"))
  .leftJoinLateral(
    q.sub(
      q.select(q.c("events.id"))
        .from(q.t("events"))
        .where(q.eq(q.c("events.user_id"), q.c("users.id"))),
    ).as(q.c("user_events")),
    q.raw`TRUE`,
  )

const query = q
  .select(q.c("users.id"), q.c("events.id"))
  .from(q.t("users"))
  .leftJoinLateral(
    q.sub(
      q.select(q.c("events.id"))
        .from(q.t("events"))
        .where(q.eq(q.c("events.user_id"), q.c("users.id"))),
    ).as(q.c("user_events")),
    q.raw`TRUE`,
  )

Natural Join

Natural join helpers emit NATURAL with the join keyword.

const query = q
  .select(q.c("users.id"), q.c("profiles.bio"))
  .from(q.t("users"))
  .naturalLeftJoin(q.t("profiles"))

const query = q
  .select(q.c("users.id"), q.c("profiles.bio"))
  .from(q.t("users"))
  .naturalLeftJoin(q.t("profiles"))