Syntax

Summary

A summary of PRQL syntax

SyntaxUsageExample
|Pipefrom employees | select first_name
=Assigns & Aliasesfrom e = employees
derive total = (sum salary)
:Named args & Parametersinterp lower:0 1600 sat_score
[]Listselect [id, amount]
()Precedencederive fahrenheit = (celsius - 32) * 1.8
#Comment# A comment
@Date & times@2021-01-01
==Equality comparisonjoin s=salaries [s.emp_id == e.id]
->Function definitionsfunc add a b -> a + b
+/-Sort ordersort [-amount, +date]
??Coalesceamount ?? 0
<type>Annotations@2021-01-01<datetime>

Pipes

Pipes — the connection between transforms that make up a pipeline — can be either line breaks or a pipe character (|).

In almost all situations, line-breaks pipe the result of a line’s transform into the transform on the following line. For example, the filter transform operates on the result of from employees (which is just the employees table), and the select transform operates on the result of the filter transform.

PRQL

from employees
filter department == "Product"
select [first_name, last_name]

SQL

SELECT
  first_name,
  last_name
FROM
  employees
WHERE
  department = 'Product'

In the place of a line-break, it’s also possible to use the | character to pipe results, such that this is equivalent:

PRQL

from employees | filter department == "Product" | select [first_name, last_name]

SQL

SELECT
  first_name,
  last_name
FROM
  employees
WHERE
  department = 'Product'

A line-break doesn’t create a pipeline in a couple of cases:

  • within a list (e.g. the derive examples below),
  • when the following line is a new statement, which starts with a keyword of func, table or from.

Lists

Lists are represented with [], and can span multiple lines. A final trailing comma is optional.

PRQL

derive [x = 1, y = 2]
derive [
  a = x,
  b = y
]
derive [
  c = a,
  d = b,
]

SQL

SELECT
  1 AS x,
  2 AS y,
  1 AS a,
  2 AS b,
  1 AS c,
  2 AS d

Most transforms can take either a list or a single item, so these are equivalent:

PRQL

from employees
select [first_name]

SQL

SELECT
  first_name
FROM
  employees

PRQL

from employees
select first_name

SQL

SELECT
  first_name
FROM
  employees

Parentheses

Parentheses — () — are used to give precedence to inner expressions, as is the case in almost all languages / math.

In particular, parentheses are used to nest pipelines for transforms such as group and window, which take a pipeline. Here, the aggregate pipeline is applied to each group of unique title and country values.

PRQL

from employees
group [title, country] (
  aggregate [
    average salary,
    ct = count
  ]
)

SQL

SELECT
  title,
  country,
  AVG(salary),
  COUNT(*) AS ct
FROM
  employees
GROUP BY
  title,
  country

Comments

Comments are represented by #. Currently only single line comments exist.

PRQL

from employees  # Comment 1
# Comment 2
aggregate [average salary]

SQL

SELECT
  AVG(salary)
FROM
  employees

Backticks

To use identifiers that are otherwise invalid, surround them with backticks. Depending on the dialect, these will remain as backticks or be converted to double-quotes

PRQL

prql dialect:mysql
from employees
select `first name`

SQL

SELECT
  `first name`
FROM
  employees

PRQL

prql dialect:postgres
from employees
select `first name`

SQL

SELECT
  "first name"
FROM
  employees

BigQuery also uses backticks to surround project & dataset names (even if valid identifiers) in the SELECT statement:

PRQL

prql dialect:bigquery
from `project-foo.dataset.table`
join `project-bar.dataset.table` [col_bax]

SQL

SELECT
  `project-foo.dataset.table`.*,
  `project-bar.dataset.table`.*,
  col_bax
FROM
  `project-foo.dataset.table`
  JOIN `project-bar.dataset.table` USING(col_bax)