Joins

Combine data from multiple tables with genaql's intuitive join syntax.

INNER JOIN

Returns only matching rows from both tables:

inner-join.ts

cook`main:users link:orders match:users.id=orders.user_id slay:users.name,orders.total`
// → SELECT users.name, orders.total
//   FROM users
//   INNER JOIN orders ON users.id = orders.user_id

LEFT JOIN

Returns all rows from the left table, with matching rows from the right:

left-join.ts

cook`main:users link:orders/left match:users.id=orders.user_id slay:users.name,orders.total`
// → SELECT users.name, orders.total
//   FROM users
//   LEFT JOIN orders ON users.id = orders.user_id

RIGHT JOIN

right-join.ts

cook`main:users link:orders/right match:users.id=orders.user_id slay:users.name,orders.total`
// → SELECT users.name, orders.total
//   FROM users
//   RIGHT JOIN orders ON users.id = orders.user_id

FULL OUTER JOIN

full-join.ts

cook`main:users link:orders/full match:users.id=orders.user_id slay:*`
// → SELECT *
//   FROM users
//   FULL OUTER JOIN orders ON users.id = orders.user_id

Multiple Joins

multiple-joins.ts

cook`main:orders
    link:users match:orders.user_id=users.id
    link:products match:orders.product_id=products.id
    slay:users.name,products.title,orders.quantity`
// → SELECT users.name, products.title, orders.quantity
//   FROM orders
//   INNER JOIN users ON orders.user_id = users.id
//   INNER JOIN products ON orders.product_id = products.id

Table Aliases

aliases.ts

cook`main:users@u link:orders@o match:u.id=o.user_id slay:u.name,o.total`
// → SELECT u.name, o.total
//   FROM users u
//   INNER JOIN orders o ON u.id = o.user_id

Self Joins

self-join.ts

// Find employees and their managers
cook`main:employees@e link:employees@m/left match:e.manager_id=m.id slay:e.name@employee,m.name@manager`
// → SELECT e.name AS employee, m.name AS manager
//   FROM employees e
//   LEFT JOIN employees m ON e.manager_id = m.id

Join with Conditions

join-conditions.ts

// Join with additional WHERE
cook`main:users link:orders match:users.id=orders.user_id slay:* sus:orders.status=completed sus:users.active=true`
// → SELECT *
//   FROM users
//   INNER JOIN orders ON users.id = orders.user_id
//   WHERE orders.status = $1 AND users.active = $2

Performance Tip

Always specify only the columns you need in SELECT instead of using *. This reduces data transfer and improves query performance, especially with joins.