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 . |
Column names often collide across tables, so use the
table.column form in both select() and where() to avoid ambiguous column errors.