Join

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

1. 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

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

SQL

1. SELECT
2. employees.*,
3. positions.*
4. FROM
5. employees
6. LEFT JOIN positions ON employees.id = positions.employee_id

PRQL

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

SQL

1. SELECT
2. employees.*,
3. p.*
4. FROM
5. employees
6. LEFT JOIN positions AS p ON employees.id = p.employee_id

PRQL

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

SQL

1. SELECT
2. tracks.*,
3. artists.*
4. FROM
5. tracks
6. LEFT JOIN artists ON artists.id = tracks.artist_id
7. 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:

1. from shirts
2. join hats true

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

PRQL

1. from tracks
2. join side:inner artists (
3. this.id==that.artist_id
4. )

SQL

1. SELECT
2. tracks.*,
3. artists.*
4. FROM
5. tracks
6. 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

1. from employees
2. join positions (==emp_no)

SQL

1. SELECT
2. employees.*,
3. positions.*
4. FROM
5. employees
6. JOIN positions ON employees.emp_no = positions.emp_no