Chain builder

Appearance

JOIN ​

Joins combine the main table with others in a SELECT. Each join method takes the table name plus a closure that builds the ON conditions on a JoinClause accumulator — except cross_join, which by definition has no condition. Joins are SELECT-only: they are ignored for INSERT/UPDATE/DELETE. Join tables are escaped like every identifier, and if the builder has a .db() qualifier it prefixes the join tables as well.

Join kinds ​

MethodEmits
join(table, f)INNER JOIN table ON …
left_join(table, f)LEFT JOIN table ON …
right_join(table, f)RIGHT JOIN table ON …
full_outer_join(table, f)FULL OUTER JOIN table ON …
cross_join(table)CROSS JOIN table (no ON)
rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["users.id"])
    .join("orders", |j| j.on("orders.user_id", "=", "users.id"))
    .left_join("profiles", |j| j.on("profiles.user_id", "=", "users.id"))
    .to_sql();
// SELECT "users"."id" FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id" LEFT JOIN "profiles" ON "profiles"."user_id" = "users"."id"
rust
let (sql, _) = QueryBuilder::<Postgres>::table("a")
    .select(["id"])
    .right_join("b", |j| j.on("b.a_id", "=", "a.id"))
    .full_outer_join("c", |j| j.on("c.a_id", "=", "a.id"))
    .to_sql();
// SELECT "id" FROM "a" RIGHT JOIN "b" ON "b"."a_id" = "a"."id" FULL OUTER JOIN "c" ON "c"."a_id" = "a"."id"

cross_join takes no closure — a cross join has no condition:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("a")
    .select(["id"])
    .cross_join("b")
    .to_sql();
// SELECT "id" FROM "a" CROSS JOIN "b"

Dialect note — the builder renders all five kinds identically on every dialect; whether the server accepts them is up to the database. Notably MySQL has no FULL OUTER JOIN, and SQLite only gained RIGHT/FULL OUTER JOIN in 3.39 — the builder does not guard against this, the database rejects the query at execution time.

Building ON conditions ​

The closure receives an empty JoinClause and chains conditions; multiple conditions are joined with AND. There are three condition kinds:

on — column to column ​

on(col, op, col2): both sides are identifiers, escaped at compile time. The operator is a &'static str, so it cannot come from runtime input:

rust
let (sql, _) = QueryBuilder::<MySql>::table("users")
    .select(["id"])
    .join("orders", |j| j.on("orders.user_id", "=", "users.id"))
    .to_sql();
// SELECT `id` FROM `users` INNER JOIN `orders` ON `orders`.`user_id` = `users`.`id`

on_val — column to bound value ​

on_val(col, op, val): the column is escaped, the value becomes a placeholder + bind — the same discipline as WHERE. Join binds are emitted before WHERE binds, because the JOIN clause renders first:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .join("orders", |j| {
        j.on("orders.user_id", "=", "users.id")
            .on_val("orders.status", "=", "paid")
    })
    .where_eq("users.active", true)
    .to_sql();
// SELECT "id" FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id" AND "orders"."status" = $1 WHERE "users"."active" = $2
// binds == [Value::Text("paid"), Value::Bool(true)]

on_raw — verbatim fragment ​

on_raw(sql, binds) is the escape hatch for ON conditions the structured API cannot express:

⚠️ Positional placeholder contract

The fragment is emitted verbatim — it is NOT escaped and NOT renumbered; binds are appended to the running bind list in order. On Postgres you must hand-write $N numbers matching the actual bind position; on MySQL/SQLite use ?. See the Security Model for the full escape-hatch inventory.

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("a")
    .select(["id"])
    .join("b", |j| {
        j.on_raw(
            r#""b"."a_id" = "a"."id" AND "b"."n" > $1"#,
            vec![Value::I64(5)],
        )
    })
    .to_sql();
// SELECT "id" FROM "a" INNER JOIN "b" ON "b"."a_id" = "a"."id" AND "b"."n" > $1

Note that identifiers inside the raw fragment must be quoted by hand (the example writes "b"."a_id" itself).

.db() qualifies join tables too ​

A builder-level db("name") qualifier prefixes the main table and every join table — the multi-tenant assumption is that all tables live in the same tenant database:

rust
let (sql, _) = QueryBuilder::<Postgres>::table("users")
    .db("mydb")
    .select(["users.id"])
    .left_join("profiles", |j| j.on("users.id", "=", "profiles.uid"))
    .to_sql();
// SELECT "users"."id" FROM "mydb"."users" LEFT JOIN "mydb"."profiles" ON "users"."id" = "profiles"."uid"

Column references in select/on are not rewritten — qualify them with the table name as usual.