Join

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

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

Parameters

• side specifies which rows to include, defaulting to inner.
• rel - the relation to join with, possibly including an alias, e.g. a=artists.
• condition - the criteria on which to match the rows from the two relations. Theoretically, join will produce a cartesian product of the two input relations and then filter the result by the condition. It supports two additional features:
  • Names this & that: Along name this, which refers to the first input relation, condition can use name that, which refers to the second input relation.
  • Self equality operator: If the condition is an equality comparison between two columns with the same name (i.e. (this.col == that.col)), 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'

In SQL, CROSS JOIN is a join that returns each row from first relation matched with all rows from the second relation. To accomplish this, we can use condition true, which will return all rows of the cartesian product of the input relations:

from shirts
join hats true

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

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