Operators

Expressions can be composed from function calls and operations, such as 2 + 3 or ((1 + x) * -y). In the example below, note the use of expressions to calculate the alias circumference and in the filter transform.

PRQL

1. from foo
2. select {
3. circumference = diameter * 3.14159,
4. color,
5. }
6. filter circumference > 10 && color != "red"

SQL

1. WITH table_0 AS (
2. SELECT
3. diameter * 3.14159 AS circumference,
4. color
5. FROM
6. foo
7. )
8. SELECT
9. circumference,
10. color
11. FROM
12. table_0
13. WHERE
14. circumference > 10
15. AND color <> 'red'

Operator precedence

This table shows operator precedence. Use parentheses () to prioritize operations and for function calls (see the discussion below.)

Coalesce

We can coalesce values with an ?? operator. Coalescing takes either the first value or, if that value is null, the second value.

PRQL

1. from orders
2. derive amount ?? 0

SQL

1. SELECT
2. *,
3. COALESCE(amount, 0)
4. FROM
5. orders

Regex expressions

Note

This is currently experimental

To perform a case-sensitive regex search, use the ~= operator. This generally compiles to REGEXP, though differs by dialect. A regex search means that to match an exact value, the start and end need to be anchored with ^foo$.

PRQL

1. from tracks
2. filter (name ~= "Love")

SQL

1. SELECT
2. *
3. FROM
4. tracks
5. WHERE
6. REGEXP(name, 'Love')

PRQL

1. prql target:sql.duckdb

3. from artists
4. filter (name ~= "Love.*You")

SQL

1. SELECT
2. *
3. FROM
4. artists
5. WHERE
6. REGEXP_MATCHES(name, 'Love.*You')

PRQL

1. prql target:sql.bigquery

3. from tracks
4. filter (name ~= "\\bLove\\b")

SQL

1. SELECT
2. *
3. FROM
4. tracks
5. WHERE
6. REGEXP_CONTAINS(name, '\bLove\b')

PRQL

1. prql target:sql.postgres

3. from tracks
4. filter (name ~= "\\(I Can't Help\\) Falling")

SQL

1. SELECT
2. *
3. FROM
4. tracks
5. WHERE
6. name ~ '\(I Can''t Help\) Falling'

PRQL

1. prql target:sql.mysql

3. from tracks
4. filter (name ~= "With You")

SQL

1. SELECT
2. *
3. FROM
4. tracks
5. WHERE
6. REGEXP_LIKE(name, 'With You', 'c')

PRQL

1. prql target:sql.sqlite

3. from tracks
4. filter (name ~= "But Why Isn't Your Syntax More Similar\\?")

SQL

1. SELECT
2. *
3. FROM
4. tracks
5. WHERE
6. name REGEXP 'But Why Isn''t Your Syntax More Similar\?'

Parentheses

PRQL uses parentheses () for several purposes:

• Parentheses group operands to control the order of evaluation, for example: ((1 + x) * y)
• Parentheses delimit a minus sign of a function argument, for example: add (-1) (-3)
• Parentheses delimit nested function calls that contain a pipe, either the | symbol or a new line. “Nested” means within a transform; i.e. not just the main pipeline, for example: (column-name | in 0..20)
• Parentheses wrap a function call that is part of a larger expression, for example: round 0 (sum distance)

Parentheses are not required for expressions that do not contain function calls, for example: foo + bar.

Here’s a set of examples of these rules:

PRQL

1. from employees
2. # Requires parentheses, because it contains a pipe
3. derive is_proximate = (distance | in 0..20)
4. # Requires parentheses, because it's a function call
5. derive total_distance = (sum distance)
6. # `??` doesn't require parentheses, as it's not a function call
7. derive min_capped_distance = (min distance ?? 5)
8. # No parentheses needed, because no function call
9. derive travel_time = distance / 40
10. # No inner parentheses needed around `1+1` because no function call
11. derive distance_rounded_2_dp = (round 1+1 distance)
12. derive {
13. # Requires parentheses, because it contains a pipe
14. is_far = (distance | in 100..),
15. # The left value of the range requires parentheses,
16. # because of the minus sign
17. is_negative = (distance | in (-100..0)),
18. # ...this is equivalent
19. is_negative = (distance | in (-100)..0),
20. # _Technically_, this doesn't require parentheses, because it's
21. # the RHS of an assignment in a tuple
22. # (this is especially confusing)
23. average_distance = average distance,
24. }
25. # Requires parentheses because of the minus sign
26. sort (-distance)
27. # A tuple is fine too
28. sort {-distance}

SQL

1. SELECT
2. *,
3. distance BETWEEN 0 AND 20 AS is_proximate,
4. SUM(distance) OVER () AS total_distance,
5. MIN(COALESCE(distance, 5)) OVER () AS min_capped_distance,
6. (distance / 40) AS travel_time,
7. ROUND(distance, 1 + 1) AS distance_rounded_2_dp,
8. distance >= 100 AS is_far,
9. distance BETWEEN -100 AND 0,
10. distance BETWEEN -100 AND 0 AS is_negative,
11. AVG(distance) OVER () AS average_distance
12. FROM
13. employees
14. ORDER BY
15. distance DESC

For example, the snippet below produces an error because the sum function call is not in a tuple.

PRQL

1. from employees
2. derive total_distance = sum distance

Error

1. Error:
2. ╭─[:2:29]
3. │
4. 2 │ derive total_distance = sum distance
5. │                             ────┬───
6. │                                 ╰───── Unknown name
7. ───╯

…while with parentheses, it works at expected:

PRQL

1. from employees
2. derive other_distance = (sum distance)

SQL

1. SELECT
2. *,
3. SUM(distance) OVER () AS other_distance
4. FROM
5. employees

Note

We’re continuing to think whether these rules can be more intuitive. We’re also planning to make the error messages much better, so the compiler can help out.