Pipelines

The simplest pipeline

The simplest pipeline is just:

PRQL

from employees

SQL

SELECT
  employees.*
FROM
  employees

Adding transformations

We can add additional lines, each one transforms the result:

PRQL

from employees
derive gross_salary = (salary + payroll_tax)

SQL

SELECT
  employees.*,
  salary + payroll_tax AS gross_salary
FROM
  employees

…and so on:

PRQL

from employees
derive gross_salary = (salary + payroll_tax)
sort gross_salary

SQL

SELECT
  employees.*,
  salary + payroll_tax AS gross_salary
FROM
  employees
ORDER BY
  gross_salary

Compiling to SQL

When compiling to SQL, the PRQL compiler will try to represent as many transforms as possible with a single SELECT statement. When necessary it will “overflow” using CTEs (common table expressions):

PRQL

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

SQL

WITH table_0 AS (
  SELECT
    e.*,
    salary + payroll_tax AS gross_salary
  FROM
    employees AS e
  ORDER BY
    gross_salary
  LIMIT
    10
)
SELECT
  table_0.name,
  table_0.gross_salary,
  d.name
FROM
  table_0
  JOIN department AS d USING(dept_no)

See also

Syntax