Select

Picks and computes columns.

select {
  name = expression,
  # or
  column,
}
# or
select !{column}

Examples

PRQL

from employees
select name = f"{first_name} {last_name}"

SQL

SELECT
  CONCAT(first_name, ' ', last_name) AS name
FROM
  employees

PRQL

from employees
select {
  name = f"{first_name} {last_name}",
  age_eoy = dob - @2022-12-31,
}

SQL

SELECT
  CONCAT(first_name, ' ', last_name) AS name,
  dob - DATE '2022-12-31' AS age_eoy
FROM
  employees

PRQL

from employees
select first_name

SQL

SELECT
  first_name
FROM
  employees

PRQL

from e=employees
select {e.first_name, e.last_name}

SQL

SELECT
  first_name,
  last_name
FROM
  employees AS e

Excluding columns

We can use ! to exclude a list of columns. This can operate in two ways:

  • We use SELECT * EXCLUDE / SELECT * EXCEPT for the columns supplied to select !{} in dialects which support it.
  • Otherwise, the columns must have been defined prior in the query (unless all of a table’s columns are excluded); for example in another select or a group transform. In this case, we evaluate and specify the columns that should be included in the output SQL.

Some examples:

PRQL

prql target:sql.bigquery
from tracks
select !{milliseconds, bytes}

SQL

SELECT
  * EXCEPT (milliseconds, bytes)
FROM
  tracks

PRQL

from tracks
select {track_id, title, composer, bytes}
select !{title, composer}

SQL

SELECT
  track_id,
  bytes
FROM
  tracks

PRQL

from artists
derive nick = name
select !{artists.*}

SQL

SELECT
  name AS nick
FROM
  artists

Note that ! is also the NOT operator, so without the tuple it has a different meaning:

PRQL

prql target:sql.bigquery
from tracks
select !is_compilation

SQL

SELECT
  NOT is_compilation
FROM
  tracks