Pipe query syntax

Pipe query syntax is an extension to GoogleSQL that's simpler and more concise than standard query syntax. Pipe syntax supports the same operations as standard syntax, and improves some areas of SQL query functionality and usability.

For more background and details on pipe syntax design, see the research paper SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL. For an introduction to pipe syntax, see Work with pipe syntax. To see examples of more complex queries written in pipe syntax, see Analyze data using pipe syntax.

Pipe syntax

Pipe syntax has the following key characteristics:

  • Each pipe operator in pipe syntax consists of the pipe symbol, |>, an operator name, and any arguments:
    |> operator_name argument_list
  • Pipe operators can be added to the end of any valid query.
  • Pipe syntax works anywhere standard syntax is supported: in queries, views, table-valued functions (TVFs), and other contexts.
  • Pipe syntax can be mixed with standard syntax in the same query. For example, subqueries can use different syntax from the parent query.
  • A pipe operator can see every alias that exists in the table preceding the pipe.
  • A query can start with a FROM clause, and pipe operators can optionally be added after the FROM clause.

Query comparison

Consider the following table called Produce:

CREATEORREPLACETABLEProduceAS(
SELECT'apples'ASitem,2ASsales,'fruit'AScategory
UNIONALL
SELECT'carrots'ASitem,8ASsales,'vegetable'AScategory
UNIONALL
SELECT'apples'ASitem,7ASsales,'fruit'AScategory
UNIONALL
SELECT'bananas'ASitem,5ASsales,'fruit'AScategory
);
SELECT*FROMProduce;
/*---------+-------+-----------+
 | item | sales | category |
 +---------+-------+-----------+
 | apples | 2 | fruit |
 | carrots | 8 | vegetable |
 | apples | 7 | fruit |
 | bananas | 5 | fruit |
 +---------+-------+-----------*/

Compare the following equivalent queries that compute the number and total amount of sales for each item in the Produce table:

Standard syntax

SELECTitem,COUNT(*)ASnum_items,SUM(sales)AStotal_sales
FROMProduce
WHERE
item!='bananas'
ANDcategoryIN('fruit','nut')
GROUPBYitem
ORDERBYitemDESC;
/*--------+-----------+-------------+
 | item | num_items | total_sales |
 +--------+-----------+-------------+
 | apples | 2 | 9 |
 +--------+-----------+-------------*/

Pipe syntax

FROMProduce
|>WHERE
item!='bananas'
ANDcategoryIN('fruit','nut')
|>AGGREGATECOUNT(*)ASnum_items,SUM(sales)AStotal_sales
GROUPBYitem
|>ORDERBYitemDESC;
/*--------+-----------+-------------+
 | item | num_items | total_sales |
 +--------+-----------+-------------+
 | apples | 2 | 9 |
 +--------+-----------+-------------*/

Pipe operator semantics

Pipe operators have the following semantic behavior:

  • Each pipe operator performs a self-contained operation.
  • A pipe operator consumes the input table passed to it through the pipe symbol, |>, and produces a new table as output.
  • A pipe operator can reference only columns from its immediate input table. Columns from earlier in the same query aren't visible. Inside subqueries, correlated references to outer columns are still allowed.

FROM queries

In pipe syntax, a query can start with a standard FROM clause and use any standard FROM syntax, including tables, joins, subqueries, and table-valued functions (TVFs). Table aliases can be assigned to each input item using the AS alias clause.

A query with only a FROM clause, like FROM table_name, is allowed in pipe syntax and returns all rows from the table. For tables with columns, FROM table_name in pipe syntax is similar to SELECT * FROM table_name in standard syntax.

Examples

The following queries use the Produce table:

FROMProduce;
/*---------+-------+-----------+
 | item | sales | category |
 +---------+-------+-----------+
 | apples | 2 | fruit |
 | carrots | 8 | vegetable |
 | apples | 7 | fruit |
 | bananas | 5 | fruit |
 +---------+-------+-----------*/

-- Join tables in the FROM clause and then apply pipe operators.
FROM
ProduceASp1
JOINProduceASp2
USING(item)
|>WHEREitem='bananas'
|>SELECTp1.item,p2.sales;
/*---------+-------+
 | item | sales |
 +---------+-------+
 | bananas | 5 |
 +---------+-------*/

Pipe operators

GoogleSQL supports the following pipe operators. For operators that correspond or relate to similar operations in standard syntax, the operator descriptions highlight similarities and differences and link to more detailed documentation on the corresponding syntax.

Pipe operator list

Name Summary
SELECT Produces a new table with the listed columns.
EXTEND Propagates the existing table and adds computed columns.
SET Replaces the values of columns in the input table.
DROP Removes listed columns from the input table.
RENAME Renames specified columns.
AS Introduces a table alias for the input table.
WHERE Filters the results of the input table.
AGGREGATE Performs aggregation on data across groups of rows or the full input table.
DISTINCT Returns distinct rows from the input table, while preserving table aliases.
JOIN Joins rows from the input table with rows from a second table provided as an argument.
CALL Calls a table-valued function (TVF), passing the pipe input table as a table argument.
ORDER BY Sorts results by a list of expressions.
LIMIT Limits the number of rows to return in a query, with an optional OFFSET clause to skip over rows.
UNION Returns the combined results of the input queries to the left and right of the pipe operator.
INTERSECT Returns rows that are found in the results of both the input query to the left of the pipe operator and all input queries to the right of the pipe operator.
EXCEPT Returns rows from the input query to the left of the pipe operator that aren't present in any input queries to the right of the pipe operator.
TABLESAMPLE Selects a random sample of rows from the input table.
WITH Introduces one or more common table expressions (CTEs).
PIVOT Rotates rows into columns.
UNPIVOT Rotates columns into rows.
MATCH_RECOGNIZE Filters and aggregates rows based on matches.

SELECT pipe operator

|>SELECTexpression[[AS]alias][,...]
[WINDOWnameASwindow_spec,...]

Description

Produces a new table with the listed columns, similar to the outermost SELECT clause in a table subquery in standard syntax. The SELECT operator supports standard output modifiers like SELECT AS STRUCT and SELECT DISTINCT. The SELECT operator also supports window functions, including named windows. Named windows are defined using the WINDOW keyword and are only visible to the current pipe SELECT operator. The SELECT operator doesn't support aggregations or anonymization.

In pipe syntax, the SELECT operator in a query is optional. The SELECT operator can be used near the end of a query to specify the list of output columns. The final query result contains the columns returned from the last pipe operator. If the SELECT operator isn't used to select specific columns, the output includes the full row, similar to what the SELECT * statement in standard syntax produces.

In pipe syntax, the SELECT clause doesn't perform aggregation. Use the AGGREGATE operator instead.

For cases where SELECT would be used in standard syntax to rearrange columns, pipe syntax supports other operators:

Examples

FROM(SELECT'apples'ASitem,2ASsales)
|>SELECTitemASfruit_name;
/*------------+
 | fruit_name |
 +------------+
 | apples |
 +------------*/

-- Window function with a named window
FROMProduce
|>SELECTitem,sales,category,SUM(sales)OVERitem_windowAScategory_total
WINDOWitem_windowAS(PARTITIONBYcategory);
/*---------+-------+-----------+----------------+
 | item | sales | category | category_total |
 +---------+-------+-----------+----------------+
 | apples | 2 | fruit | 14 |
 | apples | 7 | fruit | 14 |
 | bananas | 5 | fruit | 14 |
 | carrots | 8 | vegetable | 8 |
 +---------+-------+-----------+----------------*/

EXTEND pipe operator

|>EXTENDexpression[[AS]alias][,...]
[WINDOWnameASwindow_spec,...]

Description

Propagates the existing table and adds computed columns, similar to SELECT *, new_column in standard syntax. The EXTEND operator supports window functions , including named windows. Named windows are defined using the WINDOW keyword and are only visible to the current EXTEND operator.

Examples

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,8ASsales
)
|>EXTENDitemIN('bananas','lemons')ASis_yellow;
/*---------+-------+------------+
 | item | sales | is_yellow |
 +---------+-------+------------+
 | apples | 2 | FALSE |
 | bananas | 8 | TRUE |
 +---------+-------+------------*/

-- Window function, with `OVER`
(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>EXTENDSUM(sales)OVER()AStotal_sales;
/*---------+-------+-------------+
 | item | sales | total_sales |
 +---------+-------+-------------+
 | apples | 2 | 15 |
 | bananas | 5 | 15 |
 | carrots | 8 | 15 |
 +---------+-------+-------------*/

-- Window function with a named window
FROMProduce
|>EXTENDSUM(sales)OVERitem_windowAScategory_total
WINDOWitem_windowAS(PARTITIONBYcategory);
/*-----------+-----------+----------------+
 | item | category | category_total |
 +----------------------------------------+
 | apples | fruit | 14 |
 | apples | fruit | 14 |
 | bananas | fruit | 14 |
 | carrots | vegetable | 8 |
 +----------------------------------------*/

SET pipe operator

|>SETcolumn_name=expression[,...]

Description

Replaces the value of a column in the input table, similar to SELECT * REPLACE (expression AS column) in standard syntax. Each referenced column must exist exactly once in the input table.

After a SET operation, the referenced top-level columns (like x) are updated, but table aliases (like t) still refer to the original row values. Therefore, t.x will still refer to the original value.

Example

(
SELECT1ASx,11ASy
UNIONALL
SELECT2ASx,22ASy
)
|>SETx=x*x,y=3;
/*---+---+
 | x | y |
 +---+---+
 | 1 | 3 |
 | 4 | 3 |
 +---+---*/

FROM(SELECT2ASx,3ASy)ASt
|>SETx=x*x,y=8
|>SELECTt.xASoriginal_x,x,y;
/*------------+---+---+
 | original_x | x | y |
 +------------+---+---+
 | 2 | 4 | 8 |
 +------------+---+---*/

DROP pipe operator

|>DROPcolumn_name[,...]

Description

Removes listed columns from the input table, similar to SELECT * EXCEPT (column) in standard syntax. Each referenced column must exist at least once in the input table.

After a DROP operation, the referenced top-level columns (like x) are removed, but table aliases (like t) still refer to the original row values. Therefore, t.x will still refer to the original value.

Example

SELECT'apples'ASitem,2ASsales,'fruit'AScategory
|>DROPsales,category;
/*--------+
 | item |
 +--------+
 | apples |
 +--------*/

FROM(SELECT1ASx,2ASy)ASt
|>DROPx
|>SELECTt.xASoriginal_x,y;
/*------------+---+
 | original_x | y |
 +------------+---+
 | 1 | 2 |
 +------------+---*/

RENAME pipe operator

|>RENAMEold_column_name[AS]new_column_name[,...]

Description

Renames specified columns. Each column to be renamed must exist exactly once in the input table. The RENAME operator can't rename value table fields, pseudo-columns, range variables, or objects that aren't columns in the input table.

After a RENAME operation, the referenced top-level columns (like x) are renamed, but table aliases (like t) still refer to the original row values. Therefore, t.x will still refer to the original value.

Example

SELECT1ASx,2ASy,3ASz
|>ASt
|>RENAMEyASrenamed_y
|>SELECT*,t.yASt_y;
/*---+-----------+---+-----+
 | x | renamed_y | z | t_y |
 +---+-----------+---+-----+
 | 1 | 2 | 3 | 2 |
 +---+-----------+---+-----*/

AS pipe operator

|>ASalias

Description

Introduces a table alias for the input table, similar to applying the AS alias clause on a table subquery in standard syntax. Any existing table aliases are removed and the new alias becomes the table alias for all columns in the row.

The AS operator can be useful after operators like SELECT, EXTEND, or AGGREGATE that add columns but can't give table aliases to them. You can use the table alias to disambiguate columns after the JOIN operator.

Example

(
SELECT"000123"ASid,"apples"ASitem,2ASsales
UNIONALL
SELECT"000456"ASid,"bananas"ASitem,5ASsales
)ASsales_table
|>AGGREGATESUM(sales)AStotal_salesGROUPBYid,item
-- AGGREGATE creates an output table, so the sales_table alias is now out of
-- scope. Add a t1 alias so the join can refer to its id column.
|>ASt1
|>JOIN(SELECT456ASid,"yellow"AScolor)ASt2
ONCAST(t1.idASINT64)=t2.id
|>SELECTt2.id,total_sales,color;
/*-----+-------------+--------+
 | id | total_sales | color |
 +-----+-------------+--------+
 | 456 | 5 | yellow |
 +-----+-------------+--------*/

WHERE pipe operator

|>WHEREboolean_expression

Description

Filters the results of the input table. The WHERE operator behaves the same as the WHERE clause in standard syntax.

In pipe syntax, the WHERE operator also replaces the HAVING clause and QUALIFY clause in standard syntax. For example, after performing aggregation with the AGGREGATE operator, use the WHERE operator instead of the HAVING clause. For window functions inside a QUALIFY clause, use window functions inside a WHERE clause instead.

Example

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>WHEREsales>=3;
/*---------+-------+
 | item | sales |
 +---------+-------+
 | bananas | 5 |
 | carrots | 8 |
 +---------+-------*/

AGGREGATE pipe operator

-- Full-table aggregation
|>AGGREGATEaggregate_expression[[AS]alias][,...]
 
-- Aggregation with grouping
|>AGGREGATE[aggregate_expression[[AS]alias][,...]]
GROUPBYgroupable_items[[AS]alias][,...]
 
-- Aggregation with grouping and shorthand ordering syntax
|>AGGREGATE[aggregate_expression[[AS]alias][order_suffix][,...]]
GROUP[ANDORDER]BYgroupable_item[[AS]alias][order_suffix][,...]
order_suffix:{ASC|DESC}[{NULLSFIRST|NULLSLAST}]

Description

Performs aggregation on data across grouped rows or an entire table. The AGGREGATE operator is similar to a query in standard syntax that contains a GROUP BY clause or a SELECT list with aggregate functions or both. In pipe syntax, the GROUP BY clause is part of the AGGREGATE operator. Pipe syntax doesn't support a standalone GROUP BY operator.

Without the GROUP BY clause, the AGGREGATE operator performs full-table aggregation and produces one output row.

With the GROUP BY clause, the AGGREGATE operator performs aggregation with grouping, producing one row for each set of distinct values for the grouping expressions.

The AGGREGATE expression list corresponds to the aggregated expressions in a SELECT list in standard syntax. Each expression in the AGGREGATE list must include an aggregate function. Aggregate expressions can also include scalar expressions (for example, sqrt(SUM(x*x))). Column aliases can be assigned using the AS operator. Window functions aren't allowed, but the EXTEND operator can be used before the AGGREGATE operator to compute window functions.

The GROUP BY clause in the AGGREGATE operator corresponds to the GROUP BY clause in standard syntax. Unlike in standard syntax, aliases can be assigned to GROUP BY items. Standard grouping operators like GROUPING SETS, ROLLUP, and CUBE are supported.

The output columns from the AGGREGATE operator include all grouping columns first, followed by all aggregate columns, using their assigned aliases as the column names.

Unlike in standard syntax, grouping expressions aren't repeated across SELECT and GROUP BY clauses. In pipe syntax, the grouping expressions are listed once, in the GROUP BY clause, and are automatically included as output columns for the AGGREGATE operator.

Because output columns are fully specified by the AGGREGATE operator, the SELECT operator isn't needed after the AGGREGATE operator unless you want to produce a list of columns different from the default.

Standard syntax

-- Aggregation in standard syntax
SELECTSUM(col1)AStotal,col2,col3,col4...
FROMtable1
GROUPBYcol2,col3,col4...

Pipe syntax

-- The same aggregation in pipe syntax
FROMtable1
|>AGGREGATESUM(col1)AStotal
GROUPBYcol2,col3,col4...

Examples

-- Full-table aggregation
(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'apples'ASitem,7ASsales
)
|>AGGREGATECOUNT(*)ASnum_items,SUM(sales)AStotal_sales;
/*-----------+-------------+
 | num_items | total_sales |
 +-----------+-------------+
 | 3 | 14 |
 +-----------+-------------*/

-- Aggregation with grouping
(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'apples'ASitem,7ASsales
)
|>AGGREGATECOUNT(*)ASnum_items,SUM(sales)AStotal_sales
GROUPBYitem;
/*---------+-----------+-------------+
 | item | num_items | total_sales |
 +---------+-----------+-------------+
 | apples | 2 | 9 |
 | bananas | 1 | 5 |
 +---------+-----------+-------------*/

Shorthand ordering syntax with AGGREGATE

The AGGREGATE operator supports a shorthand ordering syntax, which is equivalent to applying the ORDER BY operator as part of the AGGREGATE operator without repeating the column list:

-- Aggregation with grouping and shorthand ordering syntax
|>AGGREGATE[aggregate_expression[[AS]alias][order_suffix][,...]]
GROUP[ANDORDER]BYgroupable_item[[AS]alias][order_suffix][,...]
order_suffix:{ASC|DESC}[{NULLSFIRST|NULLSLAST}]

The GROUP AND ORDER BY clause is equivalent to an ORDER BY clause on all groupable_items. By default, each groupable_item is sorted in ascending order with NULL values first. Other ordering suffixes like DESC or NULLS LAST can be used for other orders.

Without the GROUP AND ORDER BY clause, the ASC or DESC suffixes can be added on individual columns in the GROUP BY list or AGGREGATE list or both. The NULLS FIRST and NULLS LAST suffixes can be used to further modify NULL sorting.

Adding these suffixes is equivalent to adding an ORDER BY clause that includes all of the suffixed columns with the suffixed grouping columns first, matching the left-to-right output column order.

Examples

Consider the following table called Produce:

/*---------+-------+-----------+
 | item | sales | category |
 +---------+-------+-----------+
 | apples | 2 | fruit |
 | carrots | 8 | vegetable |
 | apples | 7 | fruit |
 | bananas | 5 | fruit |
 +---------+-------+-----------*/

The following two equivalent examples show you how to order by all grouping columns using the GROUP AND ORDER BY clause or a separate ORDER BY clause:

-- Order by all grouping columns using GROUP AND ORDER BY.
FROMProduce
|>AGGREGATESUM(sales)AStotal_sales
GROUPANDORDERBYcategory,itemDESC;
/*-----------+---------+-------------+
 | category | item | total_sales |
 +-----------+---------+-------------+
 | fruit | bananas | 5 |
 | fruit | apples | 9 |
 | vegetable | carrots | 8 |
 +-----------+---------+-------------*/

--OrderbycolumnsusingORDERBYafterperformingaggregation.
FROMProduce
|>AGGREGATESUM(sales)AStotal_sales
GROUPBYcategory,item
|>ORDERBYcategory,itemDESC;

You can add an ordering suffix to a column in the AGGREGATE list. Although the AGGREGATE list appears before the GROUP BY list in the query, ordering suffixes on columns in the GROUP BY list are applied first.

FROMProduce
|>AGGREGATESUM(sales)AStotal_salesASC
GROUPBYitem,categoryDESC;
/*---------+-----------+-------------+
 | item | category | total_sales |
 +---------+-----------+-------------+
 | carrots | vegetable | 8 |
 | bananas | fruit | 5 |
 | apples | fruit | 9 |
 +---------+-----------+-------------*/

The previous query is equivalent to the following:

-- Order by specified grouping and aggregate columns.
FROMProduce
|>AGGREGATESUM(sales)AStotal_sales
GROUPBYitem,category
|>ORDERBYcategoryDESC,total_sales;

DISTINCT pipe operator

|>DISTINCT

Description

Returns distinct rows from the input table, while preserving table aliases.

Using the DISTINCT operator after a SELECT or UNION ALL clause is similar to using a SELECT DISTINCT clause or UNION DISTINCT clause in standard syntax, but the DISTINCT pipe operator can be applied anywhere. The DISTINCT operator computes distinct rows based on the values of all visible columns. Pseudo-columns are ignored while computing distinct rows and are dropped from the output.

The DISTINCT operator is similar to using a |> SELECT DISTINCT * clause, but doesn't expand value table fields, and preserves table aliases from the input.

Examples

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>DISTINCT
|>WHEREsales>=3;
/*---------+-------+
 | item | sales |
 +---------+-------+
 | bananas | 5 |
 | carrots | 8 |
 +---------+-------*/

In the following example, the table alias Produce can be used in expressions after the DISTINCT pipe operator.

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>ASProduce
|>DISTINCT
|>SELECTProduce.item;
/*---------+
 | item |
 +---------+
 | apples |
 | bananas |
 | carrots |
 +---------*/

By contrast, the table alias isn't visible after a |> SELECT DISTINCT * clause.

-- Error, unrecognized name: Produce
(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>ASProduce
|>SELECTDISTINCT*
|>SELECTProduce.item;

In the following examples, the DISTINCT operator doesn't expand value table fields and retains the STRUCT type in the result. By contrast, the |> SELECT DISTINCT * clause expands the STRUCT type into two columns.

SELECTASSTRUCT1x,2y
|>DISTINCT;
/*---------+
 | $struct |
 +---------+
 {
 x: 1,
 y: 2
 }
 +----------*/

SELECTASSTRUCT1x,2y
|>SELECTDISTINCT*;
/*---+---+
 | x | y |
 +---+---+
 | 1 | 2 |
 +---+---*/

The following examples show equivalent ways to generate the same results with distinct values from columns a, b, and c.

FROMtable
|>SELECTDISTINCTa,b,c;
FROMtable
|>SELECTa,b,c
|>DISTINCT;
FROMtable
|>AGGREGATE
GROUPBYa,b,c;

JOIN pipe operator

|>[join_type]JOINfrom_item[[AS]alias][{on_clause|using_clause}]

Description

Joins rows from the input table with rows from a second table provided as an argument. The JOIN operator behaves the same as the JOIN operation in standard syntax. The input table is the left side of the join and the JOIN argument is the right side of the join. Standard join inputs are supported, including tables, subqueries, UNNEST operations, and table-valued function (TVF) calls. Standard join modifiers like LEFT, INNER, and CROSS are allowed before the JOIN keyword.

An alias can be assigned to the input table on the right side of the join, but not to the input table on the left side of the join. If an alias on the input table is needed, perhaps to disambiguate columns in an ON expression, then an alias can be added using the AS operator before the JOIN arguments.

Example

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
)
|>ASproduce_sales
|>LEFTJOIN
(
SELECT"apples"ASitem,123ASid
)ASproduce_data
ONproduce_sales.item=produce_data.item
|>SELECTproduce_sales.item,sales,id;
/*---------+-------+------+
 | item | sales | id |
 +---------+-------+------+
 | apples | 2 | 123 |
 | bananas | 5 | NULL |
 +---------+-------+------*/

CALL pipe operator

|>CALLtable_function(argument[,...])[[AS]alias]

Description

Calls a table-valued function (TVF) that accepts at least one table as an argument, similar to table function calls in standard syntax.

TVFs in standard syntax can be called in the FROM clause or in a JOIN operation. These are both allowed in pipe syntax as well.

In pipe syntax, TVFs that take a table argument can also be called with the CALL operator. The first table argument comes from the input table and must be omitted in the arguments. An optional table alias can be added for the output table.

Multiple TVFs can be called sequentially without using nested subqueries.

Examples

Suppose you have TVFs with the following parameters:

  • tvf1(inputTable1, arg1 ANY TYPE) and
  • tvf2(arg2 ANY TYPE, arg3 ANY TYPE, inputTable2).

The following examples compare calling both TVFs on an input table by using standard syntax and by using the CALL pipe operator:

-- Call the TVFs without using the CALL operator.
SELECT*
FROM
tvf2(arg2,arg3,TABLEtvf1(TABLEinput_table,arg1));

-- Call the same TVFs with the CALL operator.
FROMinput_table
|>CALLtvf1(arg1)
|>CALLtvf2(arg2,arg3);

ORDER BY pipe operator

|>ORDERBYexpression[sort_options][,...]

Description

Sorts results by a list of expressions. The ORDER BY operator behaves the same as the ORDER BY clause in standard syntax. Suffixes like ASC, DESC, and NULLS LAST are supported for customizing the ordering for each expression.

In pipe syntax, the AGGREGATE operator also supports shorthand ordering suffixes to apply ORDER BY behavior more concisely as part of aggregation.

Example

(
SELECT1ASx
UNIONALL
SELECT3ASx
UNIONALL
SELECT2ASx
)
|>ORDERBYxDESC;
/*---+
 | x |
 +---+
 | 3 |
 | 2 |
 | 1 |
 +---*/

LIMIT pipe operator

|>LIMITcount[OFFSETskip_rows]

Description

Limits the number of rows to return in a query, with an optional OFFSET clause to skip over rows. The LIMIT operator behaves the same as the LIMIT and OFFSET clause in standard syntax.

Examples

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>ORDERBYitem
|>LIMIT1;
/*---------+-------+
 | item | sales |
 +---------+-------+
 | apples | 2 |
 +---------+-------*/

(
SELECT'apples'ASitem,2ASsales
UNIONALL
SELECT'bananas'ASitem,5ASsales
UNIONALL
SELECT'carrots'ASitem,8ASsales
)
|>ORDERBYitem
|>LIMIT1OFFSET2;
/*---------+-------+
 | item | sales |
 +---------+-------+
 | carrots | 8 |
 +---------+-------*/

UNION pipe operator

query
|>UNION{ALL|DISTINCT}(query)[,(query),...]

Description

Returns the combined results of the input queries to the left and right of the pipe operator. Columns are matched and rows are concatenated vertically.

The UNION pipe operator behaves the same as the UNION set operator in standard syntax. However, in pipe syntax, the UNION pipe operator can include multiple comma-separated queries without repeating the UNION syntax. Queries following the operator are enclosed in parentheses.

For example, compare the following equivalent queries:

-- Standard syntax
SELECT*FROM...
UNIONALL
SELECT1
UNIONALL
SELECT2;
-- Pipe syntax
SELECT*FROM...
|>UNIONALL
(SELECT1),
(SELECT2);

The UNION pipe operator supports the same modifiers as the UNION set operator in standard syntax, such as the BY NAME modifier (or CORRESPONDING) and LEFT | FULL [OUTER] mode prefixes.

Examples

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3])ASnumber
|>UNIONALL(SELECT1);
/*--------+
 | number |
 +--------+
 | 1 |
 | 2 |
 | 3 |
 | 1 |
 +--------*/

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3])ASnumber
|>UNIONDISTINCT(SELECT1);
/*--------+
 | number |
 +--------+
 | 1 |
 | 2 |
 | 3 |
 +--------*/

The following example shows multiple input queries to the right of the pipe operator:

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3])ASnumber
|>UNIONDISTINCT
(SELECT1),
(SELECT2);
/*--------+
 | number |
 +--------+
 | 1 |
 | 2 |
 | 3 |
 +--------*/

The following example uses the BY NAME modifier to match results by column name instead of in the order that the columns are given in the input queries.

SELECT1ASone_digit,10AStwo_digit
|>UNIONALLBYNAME
(SELECT20AStwo_digit,2ASone_digit);
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1 | 10 |
 | 2 | 20 |
 +-----------+-----------*/

Without the BY NAME modifier, the results are matched by column position in the input query and the column names are ignored.

SELECT1ASone_digit,10AStwo_digit
|>UNIONALL
(SELECT20AStwo_digit,2ASone_digit);
-- Results follow column order from queries and ignore column names.
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1 | 10 |
 | 20 | 2 |
 +-----------+-----------*/

INTERSECT pipe operator

query
|>INTERSECTDISTINCT(query)[,(query),...]

Description

Returns rows that are found in the results of both the input query to the left of the pipe operator and all input queries to the right of the pipe operator.

The INTERSECT pipe operator behaves the same as the INTERSECT set operator in standard syntax. However, in pipe syntax, the INTERSECT pipe operator can include multiple comma-separated queries without repeating the INTERSECT syntax. Queries following the operator are enclosed in parentheses.

For example, compare the following equivalent queries:

-- Standard syntax
SELECT*FROM...
INTERSECTDISTINCT
SELECT1
INTERSECTDISTINCT
SELECT2;
-- Pipe syntax
SELECT*FROM...
|>INTERSECTDISTINCT
(SELECT1),
(SELECT2);

The INTERSECT pipe operator supports the same modifiers as the INTERSECT set operator in standard syntax, such as the BY NAME modifier (or CORRESPONDING) and LEFT | FULL [OUTER] mode prefixes.

Examples

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumber
|>INTERSECTDISTINCT
(SELECT*FROMUNNEST(ARRAY<INT64>[2,3,3,5])ASnumber);
/*--------+
 | number |
 +--------+
 | 2 |
 | 3 |
 +--------*/

The following example shows multiple input queries to the right of the pipe operator:

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumber
|>INTERSECTDISTINCT
(SELECT*FROMUNNEST(ARRAY<INT64>[2,3,3,5])ASnumber),
(SELECT*FROMUNNEST(ARRAY<INT64>[3,3,4,5])ASnumber);
/*--------+
 | number |
 +--------+
 | 3 |
 +--------*/

The following example uses the BY NAME modifier to return the intersecting row from the columns despite the differing column order in the input queries.

WITH
NumbersTableAS(
SELECT1ASone_digit,10AStwo_digit
UNIONALL
SELECT2,20
UNIONALL
SELECT3,30
)
SELECTone_digit,two_digitFROMNumbersTable
|>INTERSECTDISTINCTBYNAME
(SELECT10AStwo_digit,1ASone_digit);
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1 | 10 |
 +-----------+-----------*/

Without the BY NAME modifier, the same columns in differing order are considered different columns, so the query doesn't detect any intersecting row values.

WITH
NumbersTableAS(
SELECT1ASone_digit,10AStwo_digit
UNIONALL
SELECT2,20
UNIONALL
SELECT3,30
)
SELECTone_digit,two_digitFROMNumbersTable
|>INTERSECTDISTINCT
(SELECT10AStwo_digit,1ASone_digit);
-- No intersecting values detected because columns aren't recognized as the same.
/*-----------+-----------+
 +-----------+-----------*/

EXCEPT pipe operator

query
|>EXCEPTDISTINCT(query)[,(query),...]

Description

Returns rows from the input query to the left of the pipe operator that aren't present in any input queries to the right of the pipe operator.

The EXCEPT pipe operator behaves the same as the EXCEPT set operator in standard syntax. However, in pipe syntax, the EXCEPT pipe operator can include multiple comma-separated queries without repeating the EXCEPT syntax. Queries following the operator are enclosed in parentheses.

For example, compare the following equivalent queries:

-- Standard syntax
SELECT*FROM...
EXCEPTDISTINCT
SELECT1
EXCEPTDISTINCT
SELECT2;
-- Pipe syntax
SELECT*FROM...
|>EXCEPTDISTINCT
(SELECT1),
(SELECT2);

Parentheses can be used to group set operations and control order of operations. In EXCEPT set operations, query results can vary depending on the operation grouping.

-- Default operation grouping
(
SELECT*FROM...
EXCEPTDISTINCT
SELECT1
)
EXCEPTDISTINCT
SELECT2;
-- Modified operation grouping
SELECT*FROM...
EXCEPTDISTINCT
(
SELECT1
EXCEPTDISTINCT
SELECT2
);
-- Same modified operation grouping in pipe syntax
SELECT*FROM...
|>EXCEPTDISTINCT
(
SELECT1
|>EXCEPTDISTINCT(SELECT2)
);

The EXCEPT pipe operator supports the same modifiers as the EXCEPT set operator in standard syntax, such as the BY NAME modifier (or CORRESPONDING) and LEFT | FULL [OUTER] mode prefixes.

Examples

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumber
|>EXCEPTDISTINCT
(SELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumber);
/*--------+
 | number |
 +--------+
 | 3 |
 | 4 |
 +--------*/

The following example shows multiple input queries to the right of the pipe operator:

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumber
|>EXCEPTDISTINCT
(SELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumber),
(SELECT*FROMUNNEST(ARRAY<INT64>[1,4])ASnumber);
/*--------+
 | number |
 +--------+
 | 3 |
 +--------*/

The following example groups the set operations to modify the order of operations. The first input query is used against the result of the last two queries instead of the values of the last two queries individually.

SELECT*FROMUNNEST(ARRAY<INT64>[1,2,3,3,4])ASnumber
|>EXCEPTDISTINCT
(
SELECT*FROMUNNEST(ARRAY<INT64>[1,2])ASnumber
|>EXCEPTDISTINCT
(SELECT*FROMUNNEST(ARRAY<INT64>[1,4])ASnumber)
);
/*--------+
 | number |
 +--------+
 | 1 |
 | 3 |
 | 4 |
 +--------*/

The following example uses the BY NAME modifier to return unique rows from the input query to the left of the pipe operator despite the differing column order in the input queries.

WITH
NumbersTableAS(
SELECT1ASone_digit,10AStwo_digit
UNIONALL
SELECT2,20
UNIONALL
SELECT3,30
)
SELECTone_digit,two_digitFROMNumbersTable
|>EXCEPTDISTINCTBYNAME
(SELECT10AStwo_digit,1ASone_digit);
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 2 | 20 |
 | 3 | 30 |
 +-----------+-----------*/

Without the BY NAME modifier, the same columns in differing order are considered different columns, so the query doesn't detect any common rows that should be excluded.

WITH
NumbersTableAS(
SELECT1ASone_digit,10AStwo_digit
UNIONALL
SELECT2,20
UNIONALL
SELECT3,30
)
SELECTone_digit,two_digitFROMNumbersTable
|>EXCEPTDISTINCT
(SELECT10AStwo_digit,1ASone_digit);
-- No values excluded because columns aren't recognized as the same.
/*-----------+-----------+
 | one_digit | two_digit |
 +-----------+-----------+
 | 1 | 10 |
 | 2 | 20 |
 | 3 | 30 |
 +-----------+-----------*/

TABLESAMPLE pipe operator

|>TABLESAMPLESYSTEM(percentPERCENT)

Description

Selects a random sample of rows from the input table. The TABLESAMPLE pipe operator behaves the same as TABLESAMPLE operator in standard syntax.

Example

The following example samples approximately 1% of data from a table called LargeTable:

FROMLargeTable
|>TABLESAMPLESYSTEM(1PERCENT);

WITH pipe operator

|>WITHaliasASquery,...

Description

Defines one or more common table expressions (CTEs) that the rest of the query can reference, similar to standard WITH clauses. Ignores the pipe input table and passes it through as the input to the next pipe operation.

Examples

SELECT1ASkey
|>WITHtAS(
SELECT1ASkey,'my_value'ASvalue
)
|>INNERJOINtUSING(key)
/*---------+---------+
 | key | value |
 +---------+---------+
 | 1 | my_value|
 +---------+---------*/

SELECT1ASkey
-- Define multiple CTEs.
|>WITHt1AS(
SELECT2
),t2AS(
SELECT3
)
|>UNIONALL(FROMt1),(FROMt2)
/*-----+
 | key |
 +-----+
 | 1 |
 | 2 |
 | 3 |
 +-----*/

The pipe WITH operator allows a trailing comma:

SELECT1ASkey
|>WITHt1AS(
SELECT2
),t2AS(
SELECT3
),
|>UNIONALL(FROMt1),(FROMt2)
/*-----+
 | key |
 +-----+
 | 1 |
 | 2 |
 | 3 |
 +-----*/

PIVOT pipe operator

|>PIVOT(aggregate_expressionFORinput_columnIN(pivot_column[,...]))[[AS]alias]

Description

Rotates rows into columns. The PIVOT pipe operator behaves the same as the PIVOT operator in standard syntax.

Example

(
SELECT"kale"ASproduct,51ASsales,"Q1"ASquarter
UNIONALL
SELECT"kale"ASproduct,4ASsales,"Q1"ASquarter
UNIONALL
SELECT"kale"ASproduct,45ASsales,"Q2"ASquarter
UNIONALL
SELECT"apple"ASproduct,8ASsales,"Q1"ASquarter
UNIONALL
SELECT"apple"ASproduct,10ASsales,"Q2"ASquarter
)
|>PIVOT(SUM(sales)FORquarterIN('Q1','Q2'));
/*---------+----+------+
 | product | Q1 | Q2 |
 +---------+-----------+
 | kale | 55 | 45 |
 | apple | 8 | 10 |
 +---------+----+------*/

UNPIVOT pipe operator

|>UNPIVOT(values_columnFORname_columnIN(column_to_unpivot[,...]))[[AS]alias]

Description

Rotates columns into rows. The UNPIVOT pipe operator behaves the same as the UNPIVOT operator in standard syntax.

Example

(
SELECT'kale'asproduct,55ASQ1,45ASQ2
UNIONALL
SELECT'apple',8,10
)
|>UNPIVOT(salesFORquarterIN(Q1,Q2));
/*---------+-------+---------+
 | product | sales | quarter |
 +---------+-------+---------+
 | kale | 55 | Q1 |
 | kale | 45 | Q2 |
 | apple | 8 | Q1 |
 | apple | 10 | Q2 |
 +---------+-------+---------*/

MATCH_RECOGNIZE pipe operator

|>MATCH_RECOGNIZE(
[PARTITIONBYpartition_expr[,...]]
ORDERBYorder_expr[{ASC|DESC}][{NULLSFIRST|NULLSLAST}][,...]
MEASURES{measures_expr[AS]alias}[,...]
[AFTERMATCHSKIP{PASTLASTROW|TONEXTROW}]
PATTERN(pattern)
DEFINEsymbolASboolean_expr[,...]
[OPTIONS([use_longest_match={TRUE|FALSE}])]
)

Description

Filters and aggregates rows based on matches. A match is an ordered sequence of rows that match a pattern that you specify. Matching rows works similarly to matching with regular expressions, but instead of matching characters in a string, the MATCH_RECOGNIZE operator finds matches across rows in a table. The MATCH_RECOGNIZE pipe operator behaves the same as the MATCH_RECOGNIZE clause in standard syntax.

Example

(
SELECT1asx
UNIONALL
SELECT2
UNIONALL
SELECT3
)
|>MATCH_RECOGNIZE(
ORDERBYx
MEASURES
ARRAY_AGG(high.x)AShigh_agg,
ARRAY_AGG(low.x)ASlow_agg
AFTERMATCHSKIPTONEXTROW
PATTERN(low|high)
DEFINE
lowASx<=2,
highASx>=2
);
/*----------+---------+
 | high_agg | low_agg |
 +----------+---------+
 | NULL | [1] |
 | NULL | [2] |
 | [3] | NULL |
 +----------+---------*/

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年10月16日 UTC.