Select

Picks and computes columns.

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:

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'

`