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)

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

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

SELECT
  tracks.*,
  artists.*
FROM
  tracks
  LEFT JOIN artists ON artists.id = tracks.artist_id
  AND 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
)

SELECT
  tracks.*,
  artists.*
FROM
  tracks
  JOIN artists ON tracks.id = artists.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)

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