Pipe query syntax is an extension to GoogleSQL that's simpler and moreconcise thanstandard query syntax. Pipe syntax supports thesame operations as standard syntax, and improves some areas of SQL queryfunctionality and usability.

For more background and details on pipe syntax design, see the research paperSQL Has Problems. We Can Fix Them: Pipe Syntax In SQL.For an introduction to pipe syntax, seeWork with pipe syntax.To see examples of more complex queries written in pipe syntax,seeAnalyze 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. Forexample, subqueries can use different syntax from the parent query.
• A pipe operator can see every alias that exists in the tablepreceding the pipe.
• A query canstart with aFROM clause, and pipeoperators can optionally be added after theFROM clause.

Query comparison

Consider the following table calledProduce:

CREATEORREPLACETABLEProduceAS(SELECT'apples'ASitem,2ASsales,'fruit'AScategoryUNIONALLSELECT'carrots'ASitem,8ASsales,'vegetable'AScategoryUNIONALLSELECT'apples'ASitem,7ASsales,'fruit'AScategoryUNIONALLSELECT'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 totalamount of sales for each item in theProduce table:

Standard syntax

SELECTitem,COUNT(*)ASnum_items,SUM(sales)AStotal_salesFROMProduceWHEREitem!='bananas'ANDcategoryIN('fruit','nut')GROUPBYitemORDERBYitemDESC;/*--------+-----------+-------------+ | item   | num_items | total_sales | +--------+-----------+-------------+ | apples | 2         | 9           | +--------+-----------+-------------*/

Pipe syntax

FROMProduce|>WHEREitem!='bananas'ANDcategoryIN('fruit','nut')|>AGGREGATECOUNT(*)ASnum_items,SUM(sales)AStotal_salesGROUPBYitem|>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 pipesymbol,|>, 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 standardFROM clauseand use any standardFROM syntax, including tables, joins, subqueries,andtable-valued functions (TVFs). Table aliases can beassigned to each input item using theAS alias clause.

A query with only aFROM clause, likeFROM table_name, is allowed in pipesyntax and returns all rows from the table. For tables with columns,FROM table_name in pipe syntax is similar toSELECT * FROM table_name in standard syntax.

Examples

The following queries use theProduce 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.FROMProduceASp1JOINProduceASp2USING(item)|>WHEREitem='bananas'|>SELECTp1.item,p2.sales;/*---------+-------+ | item    | sales | +---------+-------+ | bananas | 5     | +---------+-------*/

Pipe operators

GoogleSQL supports the following pipe operators. For operators thatcorrespond or relate to similar operations in standard syntax, the operatordescriptions highlight similarities and differences and link to more detaileddocumentation on the corresponding syntax.

Pipe operator list

SELECT pipe operator

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

Description

Produces a new table with the listed columns, similar to the outermostSELECT clause in a table subquery in standard syntax. TheSELECT operator supports standard output modifiers likeSELECT AS STRUCT andSELECT DISTINCT. TheSELECT operatoralso supportswindow functions,includingnamed windows. Named windows are defined using theWINDOW keyword and are only visible to the current pipeSELECT operator.TheSELECT operator doesn't support aggregations or anonymization.

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

In pipe syntax, theSELECT clause doesn't perform aggregation. Use theAGGREGATE operator instead.

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

• TheEXTEND operator adds columns.
• TheSET operator updates the value of an existingcolumn.
• TheDROP operator removes columns.
• TheRENAME operator renames columns.

Examples

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

-- Window function with a named windowFROMProduce|>SELECTitem,sales,category,SUM(sales)OVERitem_windowAScategory_totalWINDOWitem_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 toSELECT *, new_column in standard syntax. TheEXTEND operator supportswindow functions, includingnamed windows. Named windows are defined using theWINDOW keyword and are only visible to the currentEXTEND operator.

Examples

(SELECT'apples'ASitem,2ASsalesUNIONALLSELECT'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,2ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'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 windowFROMProduce|>EXTENDSUM(sales)OVERitem_windowAScategory_totalWINDOWitem_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 toSELECT * REPLACE (expression AS column) in standard syntax.Each referenced column must exist exactly once in the input table.

After aSET operation, the referenced top-level columns (likex) areupdated, but table aliases (liket) still refer to the original row values.Therefore,t.x will still refer to the original value.

Example

(SELECT1ASx,11ASyUNIONALLSELECT2ASx,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 toSELECT * EXCEPT (column) in standard syntax. Eachreferenced column must exist at least once in the input table.

After aDROP operation, the referenced top-level columns (likex) areremoved, but table aliases (liket) 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 inthe input table. TheRENAME operator can't rename value table fields,pseudo-columns, range variables, or objects that aren't columns in the inputtable.

After aRENAME operation, the referenced top-level columns (likex) arerenamed, but table aliases (liket) still refer to the original rowvalues. 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 theAS alias clause on a table subquery in standard syntax. Anyexisting table aliases are removed and the new alias becomes the table alias forall columns in the row.

TheAS operator can be useful after operators likeSELECT,EXTEND, orAGGREGATE that add columns but can't give tablealiases to them. You can use the table alias to disambiguate columns after theJOIN operator.

Example

(SELECT"000123"ASid,"apples"ASitem,2ASsalesUNIONALLSELECT"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)ASt2ONCAST(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. TheWHERE operator behaves the sameas theWHERE clause in standard syntax.

In pipe syntax, theWHERE operator also replaces theHAVING clause andQUALIFY clause instandard syntax. For example, after performing aggregation with theAGGREGATE operator, use theWHERE operatorinstead of theHAVING clause. Forwindow functions insideaQUALIFY clause, use window functions inside aWHERE clause instead.

Example

(SELECT'apples'ASitem,2ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'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. TheAGGREGATE operator is similar to a query in standard syntax that contains aGROUP BY clause or aSELECT list withaggregate functions or both. In pipe syntax, theGROUP BY clause is part of theAGGREGATE operator. Pipe syntaxdoesn't support a standaloneGROUP BY operator.

Without theGROUP BY clause, theAGGREGATE operator performs full-tableaggregation and produces one output row.

With theGROUP BY clause, theAGGREGATE operator performs aggregation withgrouping, producing one row for each set of distinct values for the groupingexpressions.

TheAGGREGATE expression list corresponds to the aggregated expressions in aSELECT list in standard syntax. Each expression in theAGGREGATE list mustinclude an aggregate function. Aggregate expressions can also include scalarexpressions (for example,sqrt(SUM(x*x))). Column aliases can be assignedusing theAS operator. Windowfunctions aren't allowed, but theEXTEND operator canbe used before theAGGREGATE operator to compute window functions.

TheGROUP BY clause in theAGGREGATE operator corresponds to theGROUP BYclause in standard syntax. Unlike in standard syntax, aliases can be assigned toGROUP BY items. Standard grouping operators likeGROUPING SETS,ROLLUP,andCUBE are supported.

The output columns from theAGGREGATE operator include all grouping columnsfirst, followed by all aggregate columns, using their assigned aliases as thecolumn names.

Unlike in standard syntax, grouping expressions aren't repeated acrossSELECTandGROUP BY clauses. In pipe syntax, the grouping expressions are listedonce, in theGROUP BY clause, and are automatically included as output columnsfor theAGGREGATE operator.

Because output columns are fully specified by theAGGREGATE operator, theSELECT operator isn't needed after theAGGREGATE operator unlessyou want to produce a list of columns different from the default.

Standard syntax

-- Aggregation in standard syntaxSELECTSUM(col1)AStotal,col2,col3,col4...FROMtable1GROUPBYcol2,col3,col4...

Pipe syntax

-- The same aggregation in pipe syntaxFROMtable1|>AGGREGATESUM(col1)AStotalGROUPBYcol2,col3,col4...

Examples

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

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

Shorthand ordering syntax withAGGREGATE

TheAGGREGATE operator supports a shorthand ordering syntax, which isequivalent to applying theORDER BY operator as partof theAGGREGATE 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}]

TheGROUP AND ORDER BY clause is equivalent to anORDER BY clause on allgroupable_items. By default, eachgroupable_item is sorted in ascendingorder withNULL values first. Other ordering suffixes likeDESC orNULLSLAST can be used for other orders.

Without theGROUP AND ORDER BY clause, theASC orDESC suffixes can beadded on individual columns in theGROUP BY list orAGGREGATE list or both.TheNULLS FIRST andNULLS LAST suffixes can be used to further modifyNULLsorting.

Adding these suffixes is equivalent to adding anORDER BY clause that includesall of the suffixed columns with the suffixed grouping columns first, matchingthe left-to-right output column order.

Examples

Consider the following table calledProduce:

/*---------+-------+-----------+ | 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 groupingcolumns using theGROUP AND ORDER BY clause or a separateORDER BY clause:

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

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

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

FROMProduce|>AGGREGATESUM(sales)AStotal_salesASCGROUPBYitem,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_salesGROUPBYitem,category|>ORDERBYcategoryDESC,total_sales;

DISTINCT pipe operator

|>DISTINCT

Description

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

Using theDISTINCT operator after aSELECT orUNION ALL clause is similarto using aSELECT DISTINCT clause orUNION DISTINCT clause in standard syntax, but theDISTINCTpipe operator can be applied anywhere. TheDISTINCT operator computes distinctrows based on the values of all visible columns. Pseudo-columns are ignoredwhile computing distinct rows and are dropped from the output.

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

Examples

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

In the following example, the table aliasProduce can be used inexpressions after theDISTINCT pipe operator.

(SELECT'apples'ASitem,2ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'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,2ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'carrots'ASitem,8ASsales)|>ASProduce|>SELECTDISTINCT*|>SELECTProduce.item;

In the following examples, theDISTINCT operator doesn't expand value tablefields and retains theSTRUCT type in the result. By contrast, the|> SELECT DISTINCT * clause expands theSTRUCT 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 withdistinct values from columnsa,b, andc.

FROMtable|>SELECTDISTINCTa,b,c;FROMtable|>SELECTa,b,c|>DISTINCT;FROMtable|>AGGREGATEGROUPBYa,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 anargument. TheJOIN operator behaves the same as theJOIN operation in standard syntax. The input table is theleft side of the join and theJOIN argument is the right side of the join.Standard join inputs are supported, including tables, subqueries,UNNESToperations, and table-valued function (TVF) calls. Standard join modifiers likeLEFT,INNER, andCROSS are allowed before theJOIN keyword.

An alias can be assigned to the input table on the right side of the join, butnot to the input table on the left side of the join. If an alias on theinput table is needed, perhaps to disambiguate columns in anON expression, then an alias can be added using theAS operator before theJOIN arguments.

Example

(SELECT'apples'ASitem,2ASsalesUNIONALLSELECT'bananas'ASitem,5ASsales)|>ASproduce_sales|>LEFTJOIN(SELECT"apples"ASitem,123ASid)ASproduce_dataONproduce_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 atable-valued function (TVF) that accepts at least one table asan argument, similar totable function calls in standard syntax.

TVFs in standard syntax can be called in theFROM clause or in aJOINoperation. These are both allowed in pipe syntax as well.

In pipe syntax, TVFs that take a table argument can also be called with theCALL operator. The first table argument comes from the input table andmust be omitted in the arguments. An optional table alias can be added for theoutput 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 tableby using standard syntax and by using theCALL pipe operator:

-- Call the TVFs without using the CALL operator.SELECT*FROMtvf2(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. TheORDER BY operator behaves the sameas theORDER BY clause in standard syntax. Suffixes likeASC,DESC, andNULLS LAST are supported for customizing the ordering foreach expression.

In pipe syntax, theAGGREGATE operator alsosupportsshorthand ordering suffixes toapplyORDER BY behavior more concisely as part of aggregation.

Example

(SELECT1ASxUNIONALLSELECT3ASxUNIONALLSELECT2ASx)|>ORDERBYxDESC;/*---+ | x | +---+ | 3 | | 2 | | 1 | +---*/

LIMIT pipe operator

|>LIMITcount[OFFSETskip_rows]

Description

Limits the number of rows to return in a query, with an optionalOFFSET clauseto skip over rows. TheLIMIT operator behaves the same as theLIMIT andOFFSET clause in standard syntax.

Examples

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

(SELECT'apples'ASitem,2ASsalesUNIONALLSELECT'bananas'ASitem,5ASsalesUNIONALLSELECT'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 thepipe operator. Columns are matched and rows are concatenated vertically.

TheUNION pipe operator behaves the same as theUNION set operator in standard syntax. However, in pipesyntax, theUNION pipe operator can include multiple comma-separated querieswithout repeating theUNION syntax. Queries following the operatorare enclosed in parentheses.

For example, compare the following equivalent queries:

-- Standard syntaxSELECT*FROM...UNIONALLSELECT1UNIONALLSELECT2;-- Pipe syntaxSELECT*FROM...|>UNIONALL(SELECT1),(SELECT2);

TheUNION pipe operator supports the same modifiers as theUNION set operator in standard syntax, such as theBY NAME modifier (orCORRESPONDING) andLEFT | 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 pipeoperator:

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

The following example uses theBY NAMEmodifier to match results by column name instead of in theorder 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 theBY NAME modifier,the results are matched by column position in the input query and the columnnames 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 leftof the pipe operator and all input queries to the right of the pipeoperator.

TheINTERSECT pipe operator behaves the same as theINTERSECT set operator in standard syntax. However, inpipe syntax, theINTERSECT pipe operator can include multiplecomma-separated queries without repeating theINTERSECT syntax. Queriesfollowing the operator are enclosed in parentheses.

For example, compare the following equivalent queries:

-- Standard syntaxSELECT*FROM...INTERSECTDISTINCTSELECT1INTERSECTDISTINCTSELECT2;-- Pipe syntaxSELECT*FROM...|>INTERSECTDISTINCT(SELECT1),(SELECT2);

TheINTERSECT pipe operator supports the same modifiers as theINTERSECT set operator in standard syntax, such as theBY NAME modifier (orCORRESPONDING) andLEFT | 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 pipeoperator:

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 theBY NAMEmodifier to return the intersecting row from the columns despite the differingcolumn order in the input queries.

WITHNumbersTableAS(SELECT1ASone_digit,10AStwo_digitUNIONALLSELECT2,20UNIONALLSELECT3,30)SELECTone_digit,two_digitFROMNumbersTable|>INTERSECTDISTINCTBYNAME(SELECT10AStwo_digit,1ASone_digit);/*-----------+-----------+ | one_digit | two_digit | +-----------+-----------+ | 1         | 10        | +-----------+-----------*/

Without theBY NAME modifier, the samecolumns in differing order are considered different columns, so the querydoesn't detect any intersecting row values.

WITHNumbersTableAS(SELECT1ASone_digit,10AStwo_digitUNIONALLSELECT2,20UNIONALLSELECT3,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'tpresent in any input queries to the right of the pipe operator.

TheEXCEPT pipe operator behaves the same as theEXCEPT set operator in standard syntax. However, in pipesyntax, theEXCEPT pipe operator can include multiple comma-separatedqueries without repeating theEXCEPT syntax. Queries following theoperator are enclosed in parentheses.

For example, compare the following equivalent queries:

-- Standard syntaxSELECT*FROM...EXCEPTDISTINCTSELECT1EXCEPTDISTINCTSELECT2;-- Pipe syntaxSELECT*FROM...|>EXCEPTDISTINCT(SELECT1),(SELECT2);

Parentheses can be used to group set operations and control order of operations.InEXCEPT set operations, query results can vary depending on the operationgrouping.

-- Default operation grouping(SELECT*FROM...EXCEPTDISTINCTSELECT1)EXCEPTDISTINCTSELECT2;-- Modified operation groupingSELECT*FROM...EXCEPTDISTINCT(SELECT1EXCEPTDISTINCTSELECT2);-- Same modified operation grouping in pipe syntaxSELECT*FROM...|>EXCEPTDISTINCT(SELECT1|>EXCEPTDISTINCT(SELECT2));

TheEXCEPT pipe operator supports the same modifiers as theEXCEPT set operator in standard syntax, such as theBY NAME modifier (orCORRESPONDING) andLEFT | 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 pipeoperator:

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 ofoperations. The first input query is used against the result of the last twoqueries 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 theBY NAMEmodifier to return unique rows from the input query to the left of the pipeoperator despite the differing column order in the input queries.

WITHNumbersTableAS(SELECT1ASone_digit,10AStwo_digitUNIONALLSELECT2,20UNIONALLSELECT3,30)SELECTone_digit,two_digitFROMNumbersTable|>EXCEPTDISTINCTBYNAME(SELECT10AStwo_digit,1ASone_digit);/*-----------+-----------+ | one_digit | two_digit | +-----------+-----------+ | 2         | 20        | | 3         | 30        | +-----------+-----------*/

Without theBY NAME modifier, the same columns indiffering order are considered different columns, so the query doesn't detectany common rows that should be excluded.

WITHNumbersTableAS(SELECT1ASone_digit,10AStwo_digitUNIONALLSELECT2,20UNIONALLSELECT3,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

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

|>TABLESAMPLESYSTEM(percentPERCENT)

Description

Selects a random sample of rows from the input table. TheTABLESAMPLE pipeoperator behaves the same asTABLESAMPLE operator instandard syntax.

Example

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

FROMLargeTable|>TABLESAMPLESYSTEM(1PERCENT);

WITH pipe operator

|>WITHaliasASquery,...

Description

Defines one or more common table expressions (CTEs) that the rest of the querycan reference, similar to standardWITH clauses. Ignores thepipe 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 pipeWITH 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. ThePIVOT pipe operator behaves the same as thePIVOT operator in standard syntax.

Example

(SELECT"kale"ASproduct,51ASsales,"Q1"ASquarterUNIONALLSELECT"kale"ASproduct,4ASsales,"Q1"ASquarterUNIONALLSELECT"kale"ASproduct,45ASsales,"Q2"ASquarterUNIONALLSELECT"apple"ASproduct,8ASsales,"Q1"ASquarterUNIONALLSELECT"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. TheUNPIVOT pipe operator behaves the same as theUNPIVOT operator in standard syntax.

Example

(SELECT'kale'asproduct,55ASQ1,45ASQ2UNIONALLSELECT'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. Amatch is an ordered sequenceof rows that match a pattern that you specify.Matching rows works similarly to matching with regular expressions, butinstead of matching characters in a string, theMATCH_RECOGNIZE operator findsmatches across rows in a table. TheMATCH_RECOGNIZE pipe operator behaves thesame as theMATCH_RECOGNIZE clause in standard syntax.

Example

(SELECT1asxUNIONALLSELECT2UNIONALLSELECT3)|>MATCH_RECOGNIZE(ORDERBYxMEASURESARRAY_AGG(high.x)AShigh_agg,ARRAY_AGG(low.x)ASlow_aggAFTERMATCHSKIPTONEXTROWPATTERN(low|high)DEFINElowASx<=2,highASx>=2);/*----------+---------+ | high_agg | low_agg | +----------+---------+ | NULL     | [1]     | | NULL     | [2]     | | [3]      | NULL    | +----------+---------*/