Reference and syntax for SQL Pipe syntax.
|>
operator to chain query transformations step by step.
SQL Pipe syntax supports the same functionality as standard SQL, but can improve readability by allowing queries to flow in a linear, top-to-bottom structure, which makes it easier to express, compose and understand queries.
This syntax was first presented by Google in the research paper SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL.
SQL Pipe syntax has the following structure:
|>
, an operator name, and arguments: |> operator_name arguments
.FROM
firstFROM
first queries, which are especially useful with pipe syntax, which builds queries in a step-by-step flow, with each step transforming the result of the previous one. Starting with the FROM
clause defines the initial data source, allowing you to clearly chain joins, filters, and transformations.
A pipe query typically starts with a FROM
clause that references a table, view, subquery, join, or table-valued function.
levels
table:
SELECT
pipe operatorSELECT
pipe operator works similarly to the standard SQL SELECT
statement, and is used to specify the columns or expressions you want in the result, including column references, scalar expressions, *
wildcards, and clauses like DISTINCT
or EXCLUDE
. The SELECT
pipe operator enables flexible data selection within the pipe query flow.
<select_list>
in the SELECT
pipe operator follows the same syntax as the SELECT
statement in standard SQL. Specifically, it can contain components including column references, scalar expressions, *
wildcards, and the EXCLUDE
clause.
Example
The following code example selects the level
, name
, maxpoints
, and pointsperlap
columns from the levels
table and computes the number of laps by dividing maxpoints
by pointsperlap
:
EXTEND
pipe operatorEXTEND
pipe operator adds new computed columns to query results while keeping the existing columns so that you can perform calculations or transformations on your data without losing any original columns. This is useful when you want to enrich your results with additional insights.
EXTEND
operator propagates all the columns from the input and adds computed columns, similar to the SELECT *, <expression> [AS <alias>]
syntax in standard SQL.
These computed columns can include scalar expressions or window functions defined using the format |> EXTEND <expression> [AS <alias>] [, ...]
.
Example
The following code example adds two computed columns to the levels
table: number_of_laps
, calculated by dividing maxpoint
s by pointsperlap
, and total_max_points
, which is the cumulative sum of maxpoints
ordered by level
:
AS
pipe operatorAS
pipe operator is used to rename tables or columns within a query, similar to the AS
keyword in standard SQL. You can use it to assign aliases to tables or columns, and make your query more readable and simplify complex operations. The AS
pipe operator can help clarify the intent of your query by giving meaningful names to columns or tables, improving its structure and understanding within the pipe query flow.
table(i)
, and selects the i
column from the aliased table:
WHERE
pipe operatorWHERE
pipe operator filters the input data based on a specified condition, similar to the WHERE clause in standard SQL, and also replaces the HAVING
clause in pipe syntax, allowing you to exclude rows that don’t meet the criteria.
levels
table to include only rows where the leveltype
is FastestLap
:
LIMIT
pipe operatorLIMIT
pipe operator restricts the number of rows in the result set, similar to the LIMIT clause in standard SQL, and can optionally use OFFSET
to skip a specified number of rows, useful for limiting large result sets, pagination, or testing smaller data subsets.
AGGREGATE
pipe operatorAGGREGATE
pipe operator to perform either full table aggregation or aggregation across groups, similar to using the GROUP BY
clause in standard SQL. Use this operator to apply aggregate functions like SUM
, AVG
, or COUNT
to grouped data.
Unlike SQL, where grouping expressions need to be repeated in both the SELECT
and GROUP BY
clauses, in pipe syntax, grouping expressions are listed only once in the GROUP BY
clause and are automatically included in the output columns. The AGGREGATE
operator’s output first includes the grouping expressions, followed by the aggregated expressions, using their assigned aliases as column names.
maxpoints
from the levels
table without grouping:
maxpoints
for each leveltype
and returns the corresponding level
for each maximum value, grouping the results by leveltype
:
ORDER BY
pipe operatorORDER BY
pipe operator to sort the input data based on one or more expressions, similar to the ORDER BY
clause in standard SQL to organize query results in a meaningful order. You can use ORDER BY
to specify the sorting order with options for ascending (ASC
) or descending (DESC
) order, as well as the handling of NULL
values with NULLS FIRST
or NULLS LAST
.
levels
table by numberoflaps
in descending order and then selects the level
and numberoflaps
columns from the sorted result:
JOIN
pipe operatorJOIN
pipe operator to combine rows from two or more tables based on a related column and merge datasets in a query similar to the standard SQL JOIN
clause. You can perform different types of joins, such as INNER
, LEFT
, RIGHT
, and FULL
, to retrieve and merge data based on specific conditions.