Join

Adds columns from another table, matching rows based on a condition.

join side:{inner|left|right|full} {table} {[conditions]}

Parameters

• side decides which rows to include, defaulting to inner.
• Table reference
• List of conditions
  • The result of join operation is a cartesian (cross) product of rows from both tables, which is then filtered to match all of these conditions.
  • If name is the same from both tables, it can be expressed with only ==col.

Examples

from employees
join side:left positions [employees.id==positions.employee_id]

SELECT
  employees.*,
  positions.*
FROM
  employees
  LEFT JOIN positions ON employees.id = positions.employee_id

from employees
join side:left p=positions [employees.id==p.employee_id]

SELECT
  employees.*,
  p.*
FROM
  employees
  LEFT JOIN positions AS p ON employees.id = p.employee_id

Self equality operator

If the join conditions are of form left.x == right.x, we can use “self equality operator”:

from employees
join positions [==emp_no]

SELECT
  employees.*,
  positions.*
FROM
  employees
  JOIN positions ON employees.emp_no = positions.emp_no