Pipelines

PRQL queries are a sequence of lines (or transforms) that form a pipeline. Each line transforms the data, and passes its result to the next.

The simplest pipeline is just:

PRQL

  1. from employees

SQL

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

Adding transforms

As we add additional lines, each one transforms the result:

PRQL

  1. from employees
  2. derive gross_salary = (salary + payroll_tax)

SQL

  1. SELECT
  2. *,
  3. salary + payroll_tax AS gross_salary
  4. FROM
  5. employees

…and so on:

  1. from employees
  2. derive gross_salary = (salary + payroll_tax)
  3. sort gross_salary

Compiling to SQL

PRQL compiles the query to SQL. The PRQL compiler tries to represent as many transforms as possible with a single SELECT statement. When necessary, the compiler “overflows” and creates CTEs (common table expressions):

PRQL

  1. from e = employees
  2. derive gross_salary = (salary + payroll_tax)
  3. sort gross_salary
  4. take 10
  5. join d = department [==dept_no]
  6. select [e.name, gross_salary, d.name]

SQL

  1. WITH table_1 AS (
  2. SELECT
  3. name,
  4. salary + payroll_tax AS gross_salary,
  5. dept_no
  6. FROM
  7. employees AS e
  8. ORDER BY
  9. gross_salary
  10. LIMIT
  11. 10
  12. )
  13. SELECT
  14. table_0.name,
  15. table_0.gross_salary,
  16. d.name
  17. FROM
  18. table_1 AS table_0
  19. JOIN department AS d ON table_0.dept_no = d.dept_no

See also