# Joins

Source: https://pgcast.dev/lateral

Get rows with the rows they relate to, in one query and one typed result: a film with its cast, a crew member with their mentor. You nest a shortcut in the [`lateral`](https://pgcast.dev/select#lateral) option of `select()`; it becomes a `LEFT JOIN LATERAL`, and its result a key of each row. You already know `select()` from the [tutorial](https://pgcast.dev/first-query).

## Add the rows that point to it

A film's cast is the `roles` whose `film_id` is the film's `id`. Nest a `select()` of `roles` under a key of `lateral`, and name the film's column with `db.parent()`:

You write:

```ts
const film = await db.selectOne('films', { id: 1000 }, {
  columns: ['title'],
  lateral: {
    cast: db.select('roles', { film_id: db.parent('id') }, {
      columns: ['character', 'billing'],
      order: { by: 'billing', direction: 'ASC' },
    }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "character", $4::text, "billing") AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_cast" ON true
WHERE ("id" = $5)
LIMIT $6
```

Values: `["title", "cast", "character", "billing", 1000, 1]`

TypeScript knows:

```ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  cast: db.SQLFragment<db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  cast: db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[];
} | undefined
```

- `cast` becomes a key of the film, holding an array: `[]` when no role matches.
- `db.parent('id')` is `"films"."id"`, a column of the query around it ([`parent()`](https://pgcast.dev/sql-template#parent)). When the two columns share a name, `db.parent()` alone takes the column of its filter.
- The subquery takes the options of any `select()`, here `columns` and `order` ([Options](https://pgcast.dev/select#options)).

## Add the row it points to

Each film has one studio: `studio_id` is `NOT NULL REFERENCES studios(id)`, so the row is always there. Nest `selectExactlyOne()`, and the key holds that row, not an array:

You write:

```ts
const film = await db.selectOne('films', { id: 1000 }, {
  columns: ['title'],
  lateral: {
    studio: db.selectExactlyOne('studios', { id: db.parent('studio_id') }, { columns: ['name'] }),
    cast: db.select('roles', { film_id: db.parent('id') }, {
      columns: ['character', 'billing'],
      order: { by: 'billing', direction: 'ASC' },
    }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result, $3::text, "lateral_studio".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($4::text, "character", $5::text, "billing") AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_cast" ON true
LEFT JOIN LATERAL (
  SELECT jsonb_build_object($6::text, "name") AS result
  FROM "studios"
  WHERE ("id" = "films"."studio_id")
  LIMIT $7
) AS "lateral_studio" ON true
WHERE ("id" = $8)
LIMIT $9
```

Values: `["title", "cast", "studio", "character", "billing", "name", 1, 1000, 1]`

TypeScript knows:

```ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  studio: db.SQLFragment<db.JSONOnlyColsForTable<"studios", "name"[]>, never>;
  cast: db.SQLFragment<db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  studio: db.JSONOnlyColsForTable<"studios", "name"[]>;
  cast: db.JSONOnlyColsForTable<"roles", ("billing" | "character")[]>[];
} | undefined
```

- The joins come in the sorted order of their keys, whatever order you write them in.
- On a nullable foreign key, nest [`selectOne()`](https://pgcast.dev/select#selectOne): the key holds the row or `null`, as the mentor below.
- In a lateral, `selectExactlyOne()` checks nothing: a missing row gives `null` and no error, though its type has no `null` ([`selectExactlyOne()`](https://pgcast.dev/select#selectExactlyOne)). Keep it for a `NOT NULL` foreign key.

## Go through a join table

Films and actors are many-to-many, through `roles`. Nest a lateral in the lateral, to give each role its actor:

You write:

```ts
const film = await db.selectOne('films', { id: 1000 }, {
  columns: ['title'],
  lateral: {
    cast: db.select('roles', { film_id: db.parent('id') }, {
      columns: ['character'],
      order: { by: 'billing', direction: 'ASC' },
      lateral: {
        actor: db.selectExactlyOne('actors', { id: db.parent('actor_id') }, { columns: ['name'] }),
      },
    }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "character") ||
      jsonb_build_object($4::text, "lateral_actor".result) AS result
    FROM "roles"
    LEFT JOIN LATERAL (
      SELECT jsonb_build_object($5::text, "name") AS result
      FROM "actors"
      WHERE ("id" = "roles"."actor_id")
      LIMIT $6
    ) AS "lateral_actor" ON true
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_cast" ON true
WHERE ("id" = $7)
LIMIT $8
```

Values: `["title", "cast", "character", "actor", "name", 1, 1000, 1]`

TypeScript knows:

```ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  cast: db.SQLFragment<(db.JSONOnlyColsForTable<"roles", "character"[]> & db.LateralResult<{
    actor: db.SQLFragment<db.JSONOnlyColsForTable<"actors", "name"[]>, never>;
  }>)[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  cast: (db.JSONOnlyColsForTable<"roles", "character"[]> & db.LateralResult<{
    actor: db.SQLFragment<db.JSONOnlyColsForTable<"actors", "name"[]>, never>;
  }>)[];
} | undefined
```

`db.parent()` names a column of the nearest query around it: in `actor`, `"roles"."actor_id"`. Each role's `actor` is `{ name: string }`. Nest as deep as you need; each level adds a `LEFT JOIN LATERAL` to the same query.

## Keep only the far side

For the actors alone, without the role's columns, give the inner `lateral` one subquery rather than an object: its result takes the place of each role.

You write:

```ts
const film = await db.selectOne('films', { id: 1000 }, {
  columns: ['title'],
  lateral: {
    actors: db.select('roles', { film_id: db.parent('id') }, {
      order: { by: 'billing', direction: 'ASC' },
      lateral: db.selectExactlyOne('actors', { id: db.parent('actor_id') }),
    }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_actors".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT "lateral_passthru".result AS result
    FROM "roles"
    LEFT JOIN LATERAL (
      SELECT to_jsonb("actors".*) AS result
      FROM "actors"
      WHERE ("id" = "roles"."actor_id")
      LIMIT $3
    ) AS "lateral_passthru" ON true
    WHERE ("film_id" = "films"."id")
    ORDER BY "billing" ASC
  ) AS "sq_roles"
) AS "lateral_actors" ON true
WHERE ("id" = $4)
LIMIT $5
```

Values: `["title", "actors", 1, 1000, 1]`

TypeScript knows:

```ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  actors: db.SQLFragment<actors.JSONSelectable[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  actors: actors.JSONSelectable[];
} | undefined
```

The `order` still sorts the roles, so the actors come in billing order. With the role gone, `columns` and `extras` on `roles` would have nothing to add to: the type refuses them, with `strictNullChecks` on.

```ts
db.select('roles', { film_id: 1000 }, {
  columns: ['character'],
  lateral: db.selectExactlyOne('actors', { id: db.parent('actor_id') }),  // error TS2322
});
```

## Join a table to itself

A crew member's mentor is another row of `crew`. Give the subquery an `alias`, so that `db.parent()` still reaches the outer `crew`:

You write:

```ts
const crew = await db.select('crew', db.all, {
  columns: ['name', 'job'],
  order: { by: 'id', direction: 'ASC' },
  lateral: {
    mentor: db.selectOne('crew', { id: db.parent('mentor_id') }, { alias: 'mentor', columns: ['name'] }),
    proteges: db.count('crew', { mentor_id: db.parent('id') }, { alias: 'protege' }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "name", $2::text, "job") ||
    jsonb_build_object($3::text, "lateral_mentor".result, $4::text, "lateral_proteges".result) AS result
  FROM "crew"
  LEFT JOIN LATERAL (
    SELECT jsonb_build_object($5::text, "name") AS result
    FROM "crew" AS "mentor"
    WHERE ("id" = "crew"."mentor_id")
    LIMIT $6
  ) AS "lateral_mentor" ON true
  LEFT JOIN LATERAL (
    SELECT count(*) AS result
    FROM "crew" AS "protege"
    WHERE ("mentor_id" = "crew"."id")
  ) AS "lateral_proteges" ON true
  ORDER BY "id" ASC
) AS "sq_crew"
```

Values: `["name", "job", "mentor", "proteges", "name", 1]`

TypeScript knows:

```ts
const crew: (db.JSONOnlyColsForTable<"crew", ("name" | "job")[]> & db.LateralResult<{
  mentor: db.SQLFragment<db.JSONOnlyColsForTable<"crew", "name"[]> | undefined, never>;
  proteges: db.SQLFragment<number, never>;
}>)[]
// that is
const crew: {
  name: string;
  job: crew_job;
  mentor: db.JSONOnlyColsForTable<"crew", "name"[]> | null;
  proteges: number;
}[]
```

- `mentor` holds the row or `null`: Wilhelmina Strand has no mentor.
- [`count()`](https://pgcast.dev/select#count) gives a number, here each member's protégés.
- Without the aliases, the subquery's `FROM "crew"` hides the outer one, and `"crew"."mentor_id"` is the subquery's own row. Postgres runs it with no error, and every `mentor` comes back `null`, every count `0`:

  ```sql
  SELECT jsonb_build_object($5::text, "name") AS result
  FROM "crew"
  WHERE ("id" = "crew"."mentor_id")
  LIMIT $6
  ```

- One alias on the outer query does for both: with `alias: 'member'` there, `db.parent('mentor_id')` is `"member"."mentor_id"` ([`alias`](https://pgcast.dev/select#alias)).

## Order by a computed value

Each cinema, and the nearest other one: a self-join again, with the distance as an [`extras`](https://pgcast.dev/select#extras) key and as the `order`. A `sql` fragment in a subquery takes `db.parent()` too:

You write:

```ts
const cinemas = await db.select('cinemas', db.all, {
  columns: ['name'],
  lateral: {
    nearest: db.selectOne('cinemas', { id: db.conditions.ne(db.parent('id')) }, {
      alias: 'other',
      columns: ['name'],
      extras: { km: db.sql<s.cinemas.SQL, number>`${'location'} <-> ${db.parent('location')}` },
      order: { by: db.sql<s.cinemas.SQL>`${'location'} <-> ${db.parent('location')}`, direction: 'ASC' },
    }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "name") ||
    jsonb_build_object($2::text, "lateral_nearest".result) AS result
  FROM "cinemas"
  LEFT JOIN LATERAL (
    SELECT jsonb_build_object($3::text, "name") ||
      jsonb_build_object($4::text, "location" <-> "cinemas"."location") AS result
    FROM "cinemas" AS "other"
    WHERE (("id" <> "cinemas"."id"))
    ORDER BY "location" <-> "cinemas"."location" ASC
    LIMIT $5
  ) AS "lateral_nearest" ON true
) AS "sq_cinemas"
```

Values: `["name", "nearest", "name", "km", 1]`

TypeScript knows:

```ts
const cinemas: (db.JSONOnlyColsForTable<"cinemas", "name"[]> & db.LateralResult<{
  nearest: db.SQLFragment<db.ReturningTypeForTable<"cinemas", "name"[], {
    km: db.SQLFragment<number, never>;
  }> | undefined, never>;
}>)[]
// that is
const cinemas: {
  name: string;
  nearest: (db.JSONOnlyColsForTable<"cinemas", "name"[]> & db.ExtrasResult<"cinemas", {
    km: db.SQLFragment<number, never>;
  }>) | null;
}[]
```

- `<->` is Postgres's distance between two points: kilometres here, as `location` is in kilometres east and north of the city centre.
- `km` is a `number` because the fragment says so: `db.sql<s.cinemas.SQL, number>`.
- [`ne(db.parent('id'))`](https://pgcast.dev/conditions#conditions.ne) leaves the cinema itself out.
- For the three nearest, nest `select()` with [`limit: 3`](https://pgcast.dev/select#order-limit-offset-and-withties): `nearest` becomes an array.

## Watch for

- **`strictNullChecks`**: without it (or `strict`), `columns`, `extras` and `lateral` leave the type as the full row, and it refuses nothing ([Strict](https://pgcast.dev/select#options)).
- **An unchecked join**: `db.parent()` takes a column of any table, so a join on the wrong column compiles. Postgres refuses a column the parent doesn't have: `db.parent('billing')` under `films` fails with 42703, "column films.billing does not exist".
- **[`withTies`](https://pgcast.dev/select#order-limit-offset-and-withties) in a nested `selectOne()`**: it writes `FETCH FIRST $2 ROWS WITH TIES`, which keeps every tied row: under an outer `select()`, the parent row comes back once per tie; under `selectOne()`, with one of them. In a nested `select()`, the ties stay in the array. Unchanged from Zapatos 6.6.1, and open as [#164](https://github.com/AlexandreDemartin/pgcast/issues/164).
- **A `lock`**: without `of`, `FOR UPDATE` reaches the subqueries, and Postgres refuses the query (0A000): "FOR UPDATE is not allowed with aggregate functions" under `select()` or `count()`, "FOR UPDATE cannot be applied to the nullable side of an outer join" under `selectOne()`. Name the table ([`lock`](https://pgcast.dev/select#lock)):

You write:

```ts
const film = await db.selectOne('films', { id: 1000 }, {
  columns: ['title'],
  lateral: { cast: db.select('roles', { film_id: db.parent('id') }, { columns: ['character'] }) },
  lock: { for: 'UPDATE', of: 'films' },
}).run(pool);
```

Postgres runs:

```sql
SELECT jsonb_build_object($1::text, "title") ||
  jsonb_build_object($2::text, "lateral_cast".result) AS result
FROM "films"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "character") AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
  ) AS "sq_roles"
) AS "lateral_cast" ON true
WHERE ("id" = $4)
LIMIT $5
FOR UPDATE OF "films"
```

Values: `["title", "cast", "character", 1000, 1]`

TypeScript knows:

```ts
const film: (db.JSONOnlyColsForTable<"films", "title"[]> & db.LateralResult<{
  cast: db.SQLFragment<db.JSONOnlyColsForTable<"roles", "character"[]>[], never>;
}>) | undefined
// that is
const film: {
  title: string;
  cast: db.JSONOnlyColsForTable<"roles", "character"[]>[];
} | undefined
```

`FOR UPDATE OF "films"` locks the film's row, and only it, until the transaction ends: run it on a [`txnClient`](https://pgcast.dev/transaction#transaction). The rows of `roles` are read, not locked.

## See also

- [`lateral`](https://pgcast.dev/select#lateral), [`alias`](https://pgcast.dev/select#alias) and [`extras`](https://pgcast.dev/select#extras): each option, the SQL it writes, its type.
- [`parent()`](https://pgcast.dev/sql-template#parent): where it works, and what it compiles to.
- [Why pgcast](https://pgcast.dev/why): why it works this way.