Join

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

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

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'

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

Self equality operator

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