Skip to main content

Common Table Expressions (CTE)

CTEs allow you to build reusable query fragments and simplify complex queries by breaking them into named subqueries.

Example Usage​

const users = await sql
  .from(User)
  .with("users_cte", (qb) => qb.select("name"))
  .many();

Types of CTEs​

Normal CTE​

const users = await sql
  .from(User)
  .with("users_cte", (qb) => qb.select("name"))
  .with("users_cte2", (qb) => qb.select("age"))
  .many();

Recursive CTE​

const users = await sql
  .from(User)
  .withRecursive("users_cte", (qb) => qb.select("name"))
  .withRecursive("users_cte2", (qb) => qb.select("age"))
  .many();

Materialized CTE (PostgreSQL/CockroachDB only)​

const users = await sql
  .from(User)
  .withMaterialized("users_cte", (qb) => qb.select("name"))
  .many();

CTEs can be used for:

  • Recursive queries
  • Simplifying multi-step data transformations
  • Improving query readability

See also:

Next: MongoDB Introduction