Select

Picks and computes columns.

  1. select {
  2. name = expression,
  3. # or
  4. column,
  5. }
  6. # or
  7. select !{column}

Examples

PRQL

  1. from employees
  2. select name = f"{first_name} {last_name}"

SQL

  1. SELECT
  2. CONCAT(first_name, ' ', last_name) AS name
  3. FROM
  4. employees

PRQL

  1. from employees
  2. select {
  3. name = f"{first_name} {last_name}",
  4. age_eoy = dob - @2022-12-31,
  5. }

SQL

  1. SELECT
  2. CONCAT(first_name, ' ', last_name) AS name,
  3. dob - DATE '2022-12-31' AS age_eoy
  4. FROM
  5. employees

PRQL

  1. from employees
  2. select first_name

SQL

  1. SELECT
  2. first_name
  3. FROM
  4. employees

PRQL

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

SQL

  1. SELECT
  2. first_name,
  3. last_name
  4. FROM
  5. 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

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

SQL

  1. SELECT
  2. *
  3. EXCEPT
  4. (milliseconds, bytes)
  5. FROM
  6. tracks

PRQL

  1. from tracks
  2. select {track_id, title, composer, bytes}
  3. select !{title, composer}

SQL

  1. SELECT
  2. track_id,
  3. bytes
  4. FROM
  5. tracks

PRQL

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

SQL

  1. SELECT
  2. name AS nick
  3. FROM
  4. artists

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

PRQL

  1. prql target:sql.bigquery
  2. from tracks
  3. select !is_compilation

SQL

  1. SELECT
  2. NOT is_compilation
  3. FROM
  4. tracks