LATERAL joins

A subquery inside FROM that can reference earlier tables in the same FROM list. The standard answer to top-N per group and a handful of other patterns plain LEFT JOINcan't express.

#The signature

7 lines


1lateralJoin(
2  joinType: 'INNER' | 'LEFT' | 'CROSS',
3  alias: string,
4  subquery: CompiledQuery,
5  onCondition?: string,    // default 'TRUE'
6  dialect?: Dialect,       // default postgresDialect
7): LateralJoin             // { sql: string; params: unknown[] }

lateralJoin returns a fragment, not a full query — splice the sql into your parent query's FROM list and merge params into the parent's parameter list. onCondition defaults to TRUE because the lateral correlation itself is usually the implicit join.

#Top-3 posts per author

20 lines


1import { lateralJoin } from 'zanith';
2 
3const subquery = {
4  sql: `SELECT id, title, created_at
5        FROM posts
6        WHERE author_id = u.id
7        ORDER BY created_at DESC
8        LIMIT 3`,
9  params: [],
10};
11 
12const join = lateralJoin('LEFT', 'recent', subquery);
13// join.sql = LEFT JOIN LATERAL (SELECT …) AS "recent" ON TRUE
14 
15const sql = `
16  SELECT u.id, u.email, recent.title, recent.created_at
17  FROM users u
18  ${join.sql}
19`;
20const { rows } = await adapter.executeRaw(sql, join.params);

The lateral subquery sees u.id from the outer FROM— that's the whole reason for using LATERAL. A non-lateral LEFT JOIN can't reference u.id from inside its own subquery.

#Three join types

Type	When to use
`'LEFT'`	Want the outer row even when the lateral subquery returns nothing.
`'INNER'`	Drop outer rows whose lateral subquery is empty.
`'CROSS'`	No condition needed (the lateral correlation does the work). Equivalent to `INNER` with `ON TRUE`.

#When LATERAL is the right tool

Pattern	Why LATERAL
Top-N per group	Window functions can do it but cost more on large groups; LATERAL+LIMIT lets the planner use an index.
A computed value used by both `SELECT` and `WHERE`	Compute once in a lateral, reference in both clauses.
Iterating a row's array column with subquery filters	`unnest()` inside the lateral, then filter.
Per-row aggregates that depend on the outer row's values	The lateral subquery can reference the outer row directly.

#Parameter binding

The subquery argument is a CompiledQuery — its params become join.paramsverbatim. The subquery's placeholder numbers must match the position they will hold in the parent query's final parameter list (renumber if you're composing several sources).