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