Skip to content

Subqueries Unions and CTEs

Ahmad Faiz Kamaludin edited this page Oct 18, 2026 · 2 revisions

On this page: Subqueries · Unions · Common table expressions · Recursive CTEs · CTEs on writes

Subqueries

Pass a *db.SelectBuilder wherever a value, list or table is expected:

Where("score", ">", conn.Select().From("scores").SelectRaw("avg(v)"))   // scalar subquery
WhereIn("id", conn.Select().From("bans").Select("user_id"))
WhereExists(sub)
WhereNotExists(sub)
SelectSub(sub, "alias")
FromSub(sub, "alias")
JoinSub(sub, "alias", ...)
  • Build the inner query with conn.Select().From("t").
  • Bind arguments are numbered across the whole statement. $1, $2, ... stay correct however deeply queries nest.
  • Subqueries are copied when added. See Core Concepts.
  • A nil subquery records an error instead of panicking.

Unions

a := conn.Select().From("a").Select("id").Where("x", 1)
b := conn.Select().From("b").Select("id")

a.Union(b).UnionAll(conn.Select().From("c").Select("id")).OrderBy("id").Limit(5)
// (SELECT "id" FROM "a" WHERE "x" = $1) UNION (SELECT "id" FROM "b")
//   UNION ALL (SELECT "id" FROM "c") ORDER BY "id" ASC LIMIT 5
  • After the first union, OrderBy, Limit and Offset apply to the combined result.
  • Ordering and limits set before the union stay on the first query.
  • SQLite wraps each member as SELECT * FROM (...).

Common table expressions

With adds a named query in front of the statement. Reference it by name in From, joins or subqueries.

active := conn.Select().From("users").Where("active", true)

conn.Select().From("au").With("au", active).Where("age", ">", 30)
// WITH "au" AS (SELECT * FROM "users" WHERE "active" = $1) SELECT * FROM "au" WHERE "age" > $2

conn.Select().From("a").With("a",
    conn.Select().From("users").Select("id", "name"),
    db.CTEColumns("uid", "uname"),
)
// WITH "a" ("uid", "uname") AS (SELECT "id", "name" FROM "users") SELECT * FROM "a"

Options

Options follow the query and combine freely.

Option Effect
db.CTEColumns(cols...) Name the CTE's columns
db.CTEMaterialized() / db.CTENotMaterialized() AS [NOT] MATERIALIZED. PostgreSQL and SQLite only. MySQL and MariaDB return ErrUnsupported. Not both together
db.CTEUnionDistinct() UNION instead of UNION ALL (WithRecursive only)

Raw CTE bodies

WithRaw(name, db.Raw(sql, args...), opts...) uses caller-trusted SQL as the body and takes the same options. See Raw SQL for the safety rules.

Rules

  • A CTE name is a table reference, so the table prefix applies to it just as it does in From.
  • Queries passed in are copied when added.
  • Duplicate or qualified names return ErrInvalidArgs.
  • A select carrying CTEs works as a subquery, a union member, and inside Count, Exists and Paginate.

Recursive CTEs

WithRecursive(name, anchor, step, opts...) takes the starting query and the recursive member that refers back to name, joined with UNION ALL.

anchor := conn.Select().From("categories").Where("id", 1)
step := conn.Select().From("categories as c").Select("c.*").Join("tree", "tree.id", "c.parent_id")

conn.Select().From("tree").WithRecursive("tree", anchor, step)
// WITH RECURSIVE "tree" AS (
//   SELECT * FROM "categories" WHERE "id" = $1
//   UNION ALL
//   SELECT "c".* FROM "categories" AS "c" INNER JOIN "tree" ON "tree"."id" = "c"."parent_id"
// ) SELECT * FROM "tree"
  • Members are written without parentheses, because SQLite rejects a recursive reference inside a subquery.
  • Neither query is modified. The anchor must not have unions of its own.
  • On MySQL, recursion depth is capped by cte_max_recursion_depth (default 1000). A deeper CTE fails at run time.

CTEs on writes

With, WithRaw and WithRecursive also exist on update, delete and insert builders.

gone := conn.Select("id").From("users").Where("active", false)

conn.Delete("posts").With("gone", gone).WhereIn("user_id", conn.Select("id").From("gone"))
// WITH "gone" AS (SELECT "id" FROM "users" WHERE "active" = $1)
//   DELETE FROM "posts" WHERE "user_id" IN (SELECT "id" FROM "gone")
Statement Behaviour
Update, delete Clause written before the verb on PostgreSQL, MySQL 8 and SQLite. MariaDB returns ErrUnsupported: filter with WhereIn("id", subquery) there
Insert CTEs move into the SELECT given to Using (INSERT INTO t (cols) WITH ... SELECT ...), valid on every dialect. An insert with With but no Using (Values, Rows, Upsert) returns ErrUnsupported

Version floors (vendor-documented, untested here): CTEs need MySQL 8.0, MariaDB 10.2 and SQLite 3.8.3. MATERIALIZED needs PostgreSQL 12 or SQLite 3.35.

Clone this wiki locally