CTE & Subqueries

Common Table Expressions (CTEs) and UNION let you compose queries without string SQL. MountSQLI compiles both into the QueryPlan.

WITH (CTE)

.with(name, queryBuilder) defines a named CTE; the outer query can reference it.

const recent = db.from(orders).where("createdAt", ">", "2024-01-01");
const q = db
  .from(orders)
  .with("recent", recent)
  .select("customerId")
  .sum("total", "totalSpent")
  .groupBy("customerId")
  .all();
// → WITH "recent" AS (SELECT * FROM "orders" WHERE "createdAt" > ?)
//    SELECT "customerId", SUM("total") AS "totalSpent" FROM "recent" GROUP BY "customerId"

UNION / UNION ALL

.union(otherBuilder) combines two queries:

const active = db.from(users).where("active", "=", true);
const vip = db.from(users).where("tier", "=", "vip");

const rows = await active.union(vip).all();
// → SELECT * FROM "users" WHERE "active" = ? UNION SELECT * FROM "users" WHERE "tier" = ?

Subqueries in filters

db.from(users).whereExists(
  db.from(orders).where("customerId", "=", "users.id"),
);

Real-world example

Recent orders with a running total per customer:

export async function runningTotals() {
  const recent = db.from(orders).where("createdAt", ">", "2024-01-01");
  return db
    .from(orders)
    .with("recent", recent)
    .window("running", {
      fn: "SUM",
      column: "total",
      partitionBy: ["customerId"],
      orderBy: [{ column: "createdAt", dir: "asc" }],
    })
    .all();
}

Best practices

Use CTEs to make complex queries readable and reusable.
Prefer union over ORing disparate conditions when shapes align.
Reference CTEs by name in select/join.

Common mistakes

Returning mismatched columns from union sides.
Forgetting the CTE must be defined before it is referenced.

Window Functions — pair with CTEs.
Filtering — whereExists.