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.

from foo
select {
  circumference = diameter * 3.14159,
  color,
}
filter circumference > 10 && color != "red"

WITH table_0 AS (
  SELECT
    diameter * 3.14159 AS circumference,
    color
  FROM
    foo
)
SELECT
  circumference,
  color
FROM
  table_0
WHERE
  circumference > 10
  AND color <> 'red'

Operator precedence

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

| Group | Operators | Precedence | Associativity | | ———––: | ————————— | :––––: | :———–: | — | — | | parentheses | () | 0 | see below | | identifier dot | . | 1 | | | unary | - + ! == | 2 | | | range | .. | 3 | | | | | mul | * / // % | 5 | left-to-right | | add | + - | 6 | left-to-right | | compare | == != <= >= < > | 7 | left-to-right | | coalesce | ?? | 8 | left-to-right | | and | && | 9 | left-to-right | | or | || | 10 | left-to-right | | function call | | 11 | |

Division and integer division

The / operator performs division that always returns a float value, while the // operator does integer division (truncated division) that always returns an integer value.

prql target:sql.sqlite

from [
  {a = 5, b = 2},
  {a = 5, b = -2},
]
select {
  div_out = a / b,
  int_div_out = a // b,
}

WITH table_0 AS (
  SELECT
    5 AS a,
    2 AS b
  UNION
  ALL
  SELECT
    5 AS a,
    -2 AS b
)
SELECT
  (a * 1.0 / b) AS div_out,
  ROUND(ABS(a / b) - 0.5) * SIGN(a) * SIGN(b) AS int_div_out
FROM
  table_0

Coalesce

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

from orders
derive amount ?? 0

SELECT
  *,
  COALESCE(amount, 0)
FROM
  orders

Regex expressions

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

from tracks
filter (name ~= "Love")

SELECT
  *
FROM
  tracks
WHERE
  REGEXP(name, 'Love')

prql target:sql.duckdb

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

SELECT
  *
FROM
  artists
WHERE
  REGEXP_MATCHES(name, 'Love.*You')

prql target:sql.bigquery

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

SELECT
  *
FROM
  tracks
WHERE
  REGEXP_CONTAINS(name, '\bLove\b')

prql target:sql.postgres

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

SELECT
  *
FROM
  tracks
WHERE
  name ~ '\(I Can''t Help\) Falling'

prql target:sql.mysql

from tracks
filter (name ~= "With You")

SELECT
  *
FROM
  tracks
WHERE
  REGEXP_LIKE(name, 'With You', 'c')

prql target:sql.sqlite

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

SELECT
  *
FROM
  tracks
WHERE
  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: math.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:

from employees
# Requires parentheses, because it contains a pipe
derive is_proximate = (distance | in 0..20)
# Requires parentheses, because it's a function call
derive total_distance = (sum distance)
# `??` doesn't require parentheses, as it's not a function call
derive min_capped_distance = (min distance ?? 5)
# No parentheses needed, because no function call
derive travel_time = distance / 40
# No inner parentheses needed around `1+1` because no function call
derive distance_rounded_2_dp = (math.round 1+1 distance)
derive {
  # Requires parentheses, because it contains a pipe
  is_far = (distance | in 100..),
  # The left value of the range requires parentheses,
  # because of the minus sign
  is_negative = (distance | in (-100..0)),
  # ...this is equivalent
  is_negative = (distance | in (-100)..0),
  # _Technically_, this doesn't require parentheses, because it's
  # the RHS of an assignment in a tuple
  # (this is especially confusing)
  average_distance = average distance,
}
# Requires parentheses because of the minus sign
sort (-distance)
# A tuple is fine too
sort {-distance}

SELECT
  *,
  distance BETWEEN 0 AND 20 AS is_proximate,
  SUM(distance) OVER () AS total_distance,
  MIN(COALESCE(distance, 5)) OVER () AS min_capped_distance,
  distance / 40 AS travel_time,
  ROUND(distance, 1 + 1) AS distance_rounded_2_dp,
  distance >= 100 AS is_far,
  distance BETWEEN -100 AND 0,
  distance BETWEEN -100 AND 0 AS is_negative,
  AVG(distance) OVER () AS average_distance
FROM
  employees
ORDER BY
  distance DESC

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

from employees
derive total_distance = sum distance

Error:
   ╭─[:2:29]
   │
 2 │ derive total_distance = sum distance
   │                             ────┬───
   │                                 ╰───── Unknown name `distance`
───╯

…while with parentheses, it works at expected:

from employees
derive other_distance = (sum distance)

SELECT
  *,
  SUM(distance) OVER () AS other_distance
FROM
  employees

Wrapping lines

Line breaks in PRQL have semantic meaning, so to wrap a single logical line into multiple physical lines, we can use \ at the beginning of subsequent physical lines:

from artists
select is_europe =
\ country == "DE"
\ || country == "FR"
\ || country == "ES"

SELECT
  country = 'DE'
  OR country = 'FR'
  OR country = 'ES' AS is_europe
FROM
  artists

Wrapping will “jump over” empty lines or lines with comments. For example, the select here is only one logical line:

from tracks
# This would be a really long line without being able to split it:
select listening_time_years = (spotify_plays + apple_music_plays + pandora_plays)
# We can toggle between lines when developing:
# \ * length_seconds
\ * length_s
#   min  hour day  year
\ / 60 / 60 / 24 / 365

SELECT
  (
    spotify_plays + apple_music_plays + pandora_plays
  ) * length_s / 60 / 60 / 24 / 365 AS listening_time_years
FROM
  tracks

See Pipes for more details on line breaks.