join.md

Join

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

join side:{inner|left|right|full} table (condition)

Parameters

• side specifies which rows to include, defaulting to inner.
• table - a reference to a relation, possibly including an alias, e.g. a=artists
• condition - a boolean condition
  • If the condition evaluates to true for a given row, the row will be joined
  • 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)

from employees
join side:left p=positions (employees.id==p.employee_id)

from tracks
join side:left artists (
  # This adds a `country` condition, as an alternative to filtering
  artists.id==tracks.artist_id && artists.country=='UK'
)

this & that can be used to refer to the current & other table respectively:

from tracks
join side:inner artists (
  this.id==that.artist_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)