Home GitHub

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:

Method SQL
innerJoin(table, first, operator, second?) INNER JOIN
leftJoin(table, first, operator, second?) LEFT JOIN
rightJoin(table, first, operator, second?) RIGHT JOIN

Argument Forms

Form Example Generated 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 =.

const rows = await MySQLPool
  .table("orders")
  .select("orders.id", "users.name", "products.title")
  .innerJoin("users", "orders.user_id", "users.id")
  .leftJoin("order_items", "orders.id", "order_items.order_id")
  .leftJoin("products", "order_items.product_id", "products.id")
  .where("orders.status", "completed")
  .get();

Table and Column Name Rules

Target Rule
Joined table Always backtick-quoted
first, second Backtick-quoted without .; passed through with .
operator Written into SQL verbatim

Column names often collide across tables, so use the table.column form in both select() and where() to avoid ambiguous column errors. The join condition holds a single comparison; for an ON clause with several AND conditions, use Raw SQL.

中文