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

In the final example above, the e representing the table / namespace is no longer available after the select statement. For example, this would raise an error:

  1. from e=employees
  2. select e.first_name
  3. filter e.first_name == "Fred" # Can't find `e.first_name`

To refer to the e.first_name column in subsequent transforms, either refer to it using first_name, or if it requires a different name, assign one in the select statement:

PRQL

  1. from e=employees
  2. select fname = e.first_name
  3. filter fname == "Fred"

SQL

  1. WITH table_1 AS (
  2. SELECT
  3. first_name AS fname
  4. FROM
  5. employees AS e
  6. )
  7. SELECT
  8. fname
  9. FROM
  10. table_1 AS table_0
  11. WHERE
  12. fname = 'Fred'

`