# 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

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

#### SQL

``````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

``````prql target:sql.sqlite

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

#### SQL

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

#### PRQL

``````from orders
derive amount ?? 0
``````

#### SQL

``````SELECT
*,
COALESCE(amount, 0)
FROM
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

``````from tracks
filter (name ~= "Love")
``````

#### SQL

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

``````

#### PRQL

``````prql target:sql.duckdb

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

#### SQL

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

``````

#### PRQL

``````prql target:sql.bigquery

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

#### SQL

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

``````

#### PRQL

``````prql target:sql.postgres

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

#### SQL

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

``````

#### PRQL

``````prql target:sql.mysql

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

#### SQL

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

``````

#### PRQL

``````prql target:sql.sqlite

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

#### SQL

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

#### PRQL

``````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}
``````

#### SQL

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

#### PRQL

``````from employees
derive total_distance = sum distance
``````

#### Error

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

…while with parentheses, it works at expected:

#### PRQL

``````from employees
derive other_distance = (sum distance)
``````

#### SQL

``````SELECT
*,
SUM(distance) OVER () AS other_distance
FROM
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.

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

#### PRQL

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

#### SQL

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

#### PRQL

``````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
``````

#### SQL

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

``````

Info

Note that PRQL differs from most languages, which use a `\` at the end of the preceding line. Because PRQL aims to be friendly for data exploration, we want to make it possible to comment out any line, including the final line, without breaking the query. This requires all lines after the first to be structured similarly, and for the character to be at the start of each following line.

See Pipes for more details on line breaks.