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

PRQL

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

SQL

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

PRQL

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

SQL

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

PRQL

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

SQL

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:

PRQL

from tracks
join side:inner artists (
  this.id==that.artist_id
)

SQL

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”:

PRQL

from employees
join positions (==emp_no)

SQL

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