Joins

Last updated


This page covers the argument forms of innerJoin(), leftJoin(), and rightJoin() and the SQL they produce.

Three Joins


All three methods share one internal join() implementation (src/MySQLPool.ts:151-174) and differ only in the join type:
MethodSQL
innerJoin(table, first, operator, second?)INNER JOIN
leftJoin(table, first, operator, second?)LEFT JOIN
rightJoin(table, first, operator, second?)RIGHT JOIN

Argument Forms


FormExampleGenerated SQL
Three arguments (equality)innerJoin("users", "orders.user_id", "users.id")INNER JOIN `users` ON orders.user_id = users.id
Four arguments (explicit operator)leftJoin("p", "a", "!=", "b")LEFT JOIN `p` ON `a` != `b`

With three arguments, the third becomes second and the operator is =.
  1. const rows = await MySQLPool
  2. .table("orders")
  3. .select("orders.id", "users.name", "products.title")
  4. .innerJoin("users", "orders.user_id", "users.id")
  5. .leftJoin("order_items", "orders.id", "order_items.order_id")
  6. .leftJoin("products", "order_items.product_id", "products.id")
  7. .where("orders.status", "completed")
  8. .get();

Table and Column Name Rules


TargetRule
Joined tableAlways backtick-quoted
first, secondBacktick-quoted without .; passed through with .

Column names often collide across tables, so use the table.column form in both select() and where() to avoid ambiguous column errors.

中文