Joins & subqueries

Once a query reaches beyond one table, the query builder gives you typed joins, IN / EXISTS subqueries, and derived-table sources. This page builds on reading data and the Product model from the hub.

For exhaustive API detail, see the reference at pkg.go.dev/liteorm.org/query.

For a join keyed by a column you control, use the typed helpers. The table identifier is quoted by the dialect; the ON condition is raw SQL (it spans tables) and may carry ? markers:

top, err := query.Select[Product](db).
  Distinct().
  InnerJoin("reviews", "reviews.product_id = products.id").
  Where("reviews.rating >= ?", 5).
  OrderBy("products.id").
  All(ctx)

The full set: InnerJoin(table, on, args...), LeftJoin, RightJoin, CrossJoin(table), and a fully raw Join(clause, args...) escape hatch when you want to write the whole join clause yourself.

Where(frag, args...) seen above is the raw, AND-joined predicate escape hatch — reach for it when a condition spans joined tables or needs SQL the typed predicates don’t cover; prefer Filter for conditions on your own model’s columns.

By default the full model column set is selected. Project(cols...) overrides the SELECT list with raw column expressions — most often to select a single column for an IN-subquery, or to pull specific columns or aggregates. For a custom result shape, Into[T, R] projects typed Fields into a result struct R — see streaming & aggregates.

Build a subquery like any other Select, then drop it into a predicate. Its columns are validated when it’s placed in the predicate, so an error surfaces from the outer query’s terminal before any SQL runs, and its bind placeholders renumber into the outer statement automatically.

For an IN-subquery, the inner query must Project exactly one column:

fiveStar := query.Select[Review](db).
  Project("product_id").
  Filter(query.Col[int64]("rating").Ge(5))

viaSub, err := query.Select[Product](db).
  Filter(query.Col[int64]("id").InQuery(fiveStar)). // NotInQuery too
  OrderBy("id").
  All(ctx)

For an EXISTS / NOT EXISTS predicate, use query.Exists(sub) / query.NotExists(sub). The subquery typically correlates to the outer query through a raw Where:

anyReview := query.Select[Review](db).
  Project("1").
  Where("reviews.product_id = products.id")

reviewed, err := query.Select[Product](db).
  Filter(query.Exists(anyReview)).
  OrderBy("id").
  All(ctx)

query.Exists (a predicate for Filter) is distinct from the SelectBuilder.Exists terminal, which executes the query and returns a bool.

To project whether a correlated subquery matches as a boolean result column (rather than filter on it), use query.ExistsField(alias, sub) in Into. Correlate the subquery to the outer row with the typed EqCol — query.Col[V]("inner").EqCol(query.Col[V]("outer").Of("table")) — instead of a raw Where. It renders a portable CASE WHEN EXISTS (...) THEN 1 ELSE 0 END, so it scans into a bool on every backend, including SQL Server:

hasReview := query.ExistsField("has_review",
  query.Select[Review](db).Filter(
    query.Col[int64]("product_id").EqCol(query.Col[int64]("id").Of("products"))))

type row struct {
  ID        int64
  Name      string
  HasReview bool
}
rows, err := query.Into[Product, row](ctx, query.Select[Product](db).OrderBy("id"),
  query.Name("id"), query.Name("name"), hasReview)

A FROM source can also be a subquery. FromSubquery[T](sess, alias, sub) selects from a derived table, and JoinSub(kind, alias, sub, on) joins one — the subquery’s placeholders renumber into the outer statement automatically:

recent := query.Select[Order](db).Where("created_at > ?", cutoff)

big, err := query.FromSubquery[Order](db, "r", recent).
  Filter(query.Col[int64]("total").Gt(1000)).
  All(ctx)

withOrders, err := query.Select[Customer](db).
  JoinSub("INNER JOIN", "o", recent, "o.customer_id = customers.id").
  All(ctx)

JoinLateral(kind, alias, sub, on) is the same, but with the LATERAL keyword so the subquery may reference columns of earlier FROM items — Postgres-only, and a clear build error elsewhere. For CTE sources (With / WithRecursive) and scalar subqueries in the SELECT list, see advanced SQL.

• Reading data — the predicates and terminals these joins build on.
• Advanced SQL — CTEs, recursive CTEs, and scalar subqueries.
• Streaming & aggregates — Into and the projection Fields used above.
• Postgres features — LATERAL joins and dialect-specific predicates.