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
FROMclause , and pipe operators can optionally be added after theFROMclause.
Query comparison
Consider the following table called Produce
:
CREATE
OR
REPLACE
TABLE
Produce
AS
(
SELECT
'apples'
AS
item
,
2
AS
sales
,
'fruit'
AS
category
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
,
'vegetable'
AS
category
UNION
ALL
SELECT
'apples'
AS
item
,
7
AS
sales
,
'fruit'
AS
category
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
,
'fruit'
AS
category
);
SELECT
*
FROM
Produce
;
/*---------+-------+-----------+
| 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
SELECT
item
,
COUNT
(
*
)
AS
num_items
,
SUM
(
sales
)
AS
total_sales
FROM
Produce
WHERE
item
!=
'bananas'
AND
category
IN
(
'fruit'
,
'nut'
)
GROUP
BY
item
ORDER
BY
item
DESC
;
/*--------+-----------+-------------+
| item | num_items | total_sales |
+--------+-----------+-------------+
| apples | 2 | 9 |
+--------+-----------+-------------*/
Pipe syntax
FROM
Produce
|
>
WHERE
item
!=
'bananas'
AND
category
IN
(
'fruit'
,
'nut'
)
|
>
AGGREGATE
COUNT
(
*
)
AS
num_items
,
SUM
(
sales
)
AS
total_sales
GROUP
BY
item
|
>
ORDER
BY
item
DESC
;
/*--------+-----------+-------------+
| 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
:
FROM
Produce
;
/*---------+-------+-----------+
| 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
Produce
AS
p1
JOIN
Produce
AS
p2
USING
(
item
)
|
>
WHERE
item
=
'bananas'
|
>
SELECT
p1
.
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
|> SELECT expression [[ AS ] alias ] [, ...] [ WINDOW name AS window_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:
- The
EXTENDoperator adds columns. - The
SEToperator updates the value of an existing column. - The
DROPoperator removes columns. - The
RENAMEoperator renames columns.
Examples
FROM
(
SELECT
'apples'
AS
item
,
2
AS
sales
)
|
>
SELECT
item
AS
fruit_name
;
/*------------+
| fruit_name |
+------------+
| apples |
+------------*/
-- Window function with a named window
FROM
Produce
|
>
SELECT
item
,
sales
,
category
,
SUM
(
sales
)
OVER
item_window
AS
category_total
WINDOW
item_window
AS
(
PARTITION
BY
category
);
/*---------+-------+-----------+----------------+
| item | sales | category | category_total |
+---------+-------+-----------+----------------+
| apples | 2 | fruit | 14 |
| apples | 7 | fruit | 14 |
| bananas | 5 | fruit | 14 |
| carrots | 8 | vegetable | 8 |
+---------+-------+-----------+----------------*/
EXTEND
pipe operator
|> EXTEND expression [[ AS ] alias ] [, ...] [ WINDOW name AS window_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'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
8
AS
sales
)
|
>
EXTEND
item
IN
(
'bananas'
,
'lemons'
)
AS
is_yellow
;
/*---------+-------+------------+
| item | sales | is_yellow |
+---------+-------+------------+
| apples | 2 | FALSE |
| bananas | 8 | TRUE |
+---------+-------+------------*/
-- Window function, with `OVER`
(
SELECT
'apples'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
EXTEND
SUM
(
sales
)
OVER
()
AS
total_sales
;
/*---------+-------+-------------+
| item | sales | total_sales |
+---------+-------+-------------+
| apples | 2 | 15 |
| bananas | 5 | 15 |
| carrots | 8 | 15 |
+---------+-------+-------------*/
-- Window function with a named window
FROM
Produce
|
>
EXTEND
SUM
(
sales
)
OVER
item_window
AS
category_total
WINDOW
item_window
AS
(
PARTITION
BY
category
);
/*-----------+-----------+----------------+
| item | category | category_total |
+----------------------------------------+
| apples | fruit | 14 |
| apples | fruit | 14 |
| bananas | fruit | 14 |
| carrots | vegetable | 8 |
+----------------------------------------*/
SET
pipe operator
|> SET column_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
(
SELECT
1
AS
x
,
11
AS
y
UNION
ALL
SELECT
2
AS
x
,
22
AS
y
)
|
>
SET
x
=
x
*
x
,
y
=
3
;
/*---+---+
| x | y |
+---+---+
| 1 | 3 |
| 4 | 3 |
+---+---*/
FROM
(
SELECT
2
AS
x
,
3
AS
y
)
AS
t
|
>
SET
x
=
x
*
x
,
y
=
8
|
>
SELECT
t
.
x
AS
original_x
,
x
,
y
;
/*------------+---+---+
| original_x | x | y |
+------------+---+---+
| 2 | 4 | 8 |
+------------+---+---*/
DROP
pipe operator
|> DROP column_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'
AS
item
,
2
AS
sales
,
'fruit'
AS
category
|
>
DROP
sales
,
category
;
/*--------+
| item |
+--------+
| apples |
+--------*/
FROM
(
SELECT
1
AS
x
,
2
AS
y
)
AS
t
|
>
DROP
x
|
>
SELECT
t
.
x
AS
original_x
,
y
;
/*------------+---+
| original_x | y |
+------------+---+
| 1 | 2 |
+------------+---*/
RENAME
pipe operator
|> RENAME old_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
SELECT
1
AS
x
,
2
AS
y
,
3
AS
z
|
>
AS
t
|
>
RENAME
y
AS
renamed_y
|
>
SELECT
*
,
t
.
y
AS
t_y
;
/*---+-----------+---+-----+
| x | renamed_y | z | t_y |
+---+-----------+---+-----+
| 1 | 2 | 3 | 2 |
+---+-----------+---+-----*/
AS
pipe operator
|> AS alias
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"
AS
id
,
"apples"
AS
item
,
2
AS
sales
UNION
ALL
SELECT
"000456"
AS
id
,
"bananas"
AS
item
,
5
AS
sales
)
AS
sales_table
|
>
AGGREGATE
SUM
(
sales
)
AS
total_sales
GROUP
BY
id
,
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.
|
>
AS
t1
|
>
JOIN
(
SELECT
456
AS
id
,
"yellow"
AS
color
)
AS
t2
ON
CAST
(
t1
.
id
AS
INT64
)
=
t2
.
id
|
>
SELECT
t2
.
id
,
total_sales
,
color
;
/*-----+-------------+--------+
| id | total_sales | color |
+-----+-------------+--------+
| 456 | 5 | yellow |
+-----+-------------+--------*/
WHERE
pipe operator
|> WHERE boolean_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'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
WHERE
sales
> =
3
;
/*---------+-------+
| item | sales |
+---------+-------+
| bananas | 5 |
| carrots | 8 |
+---------+-------*/
AGGREGATE
pipe operator
-- Full-table aggregation |> AGGREGATE aggregate_expression [[ AS ] alias ] [, ...]
-- Aggregation with grouping |> AGGREGATE [ aggregate_expression [[ AS ] alias ] [, ...]] GROUP BY groupable_items [[ AS ] alias ] [, ...]
-- Aggregation with grouping and shorthand ordering syntax |> AGGREGATE [ aggregate_expression [[ AS ] alias ] [ order_suffix ] [, ...]] GROUP [ AND ORDER ] BY groupable_item [[ AS ] alias ] [ order_suffix ] [, ...] order_suffix : { ASC | DESC } [ { NULLS FIRST | NULLS LAST } ]
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 SELECT SUM ( col1 ) AS total , col2 , col3 , col4 ... FROM table1 GROUP BY col2 , col3 , col4 ...
Pipe syntax
-- The same aggregation in pipe syntax FROM table1 |> AGGREGATE SUM ( col1 ) AS total GROUP BY col2 , col3 , col4 ...
Examples
-- Full-table aggregation
(
SELECT
'apples'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'apples'
AS
item
,
7
AS
sales
)
|
>
AGGREGATE
COUNT
(
*
)
AS
num_items
,
SUM
(
sales
)
AS
total_sales
;
/*-----------+-------------+
| num_items | total_sales |
+-----------+-------------+
| 3 | 14 |
+-----------+-------------*/
-- Aggregation with grouping
(
SELECT
'apples'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'apples'
AS
item
,
7
AS
sales
)
|
>
AGGREGATE
COUNT
(
*
)
AS
num_items
,
SUM
(
sales
)
AS
total_sales
GROUP
BY
item
;
/*---------+-----------+-------------+
| 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 [ AND ORDER ] BY groupable_item [[ AS ] alias ] [ order_suffix ] [, ...] order_suffix : { ASC | DESC } [ { NULLS FIRST | NULLS LAST } ]
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.
FROM
Produce
|
>
AGGREGATE
SUM
(
sales
)
AS
total_sales
GROUP
AND
ORDER
BY
category
,
item
DESC
;
/*-----------+---------+-------------+
| category | item | total_sales |
+-----------+---------+-------------+
| fruit | bananas | 5 |
| fruit | apples | 9 |
| vegetable | carrots | 8 |
+-----------+---------+-------------*/
--
Order
by
columns
using
ORDER
BY
after
performing
aggregation
.
FROM
Produce
|
>
AGGREGATE
SUM
(
sales
)
AS
total_sales
GROUP
BY
category
,
item
|
>
ORDER
BY
category
,
item
DESC
;
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.
FROM
Produce
|
>
AGGREGATE
SUM
(
sales
)
AS
total_sales
ASC
GROUP
BY
item
,
category
DESC
;
/*---------+-----------+-------------+
| 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.
FROM
Produce
|
>
AGGREGATE
SUM
(
sales
)
AS
total_sales
GROUP
BY
item
,
category
|
>
ORDER
BY
category
DESC
,
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'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
DISTINCT
|
>
WHERE
sales
> =
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'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
AS
Produce
|
>
DISTINCT
|
>
SELECT
Produce
.
item
;
/*---------+
| item |
+---------+
| apples |
| bananas |
| carrots |
+---------*/
By contrast, the table alias isn't visible after a |> SELECT DISTINCT *
clause.
-- Error, unrecognized name: Produce
(
SELECT
'apples'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
AS
Produce
|
>
SELECT
DISTINCT
*
|
>
SELECT
Produce
.
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.
SELECT
AS
STRUCT
1
x
,
2
y
|
>
DISTINCT
;
/*---------+
| $struct |
+---------+
{
x: 1,
y: 2
}
+----------*/
SELECT
AS
STRUCT
1
x
,
2
y
|
>
SELECT
DISTINCT
*
;
/*---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---*/
The following examples show equivalent ways to generate the same results with
distinct values from columns a
, b
, and c
.
FROM
table
|
>
SELECT
DISTINCT
a
,
b
,
c
;
FROM
table
|
>
SELECT
a
,
b
,
c
|
>
DISTINCT
;
FROM
table
|
>
AGGREGATE
GROUP
BY
a
,
b
,
c
;
JOIN
pipe operator
|> [ join_type ] JOIN from_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'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
)
|
>
AS
produce_sales
|
>
LEFT
JOIN
(
SELECT
"apples"
AS
item
,
123
AS
id
)
AS
produce_data
ON
produce_sales
.
item
=
produce_data
.
item
|
>
SELECT
produce_sales
.
item
,
sales
,
id
;
/*---------+-------+------+
| item | sales | id |
+---------+-------+------+
| apples | 2 | 123 |
| bananas | 5 | NULL |
+---------+-------+------*/
CALL
pipe operator
|> CALL table_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
,
TABLE
tvf1
(
TABLE
input_table
,
arg1
));
-- Call the same TVFs with the CALL operator.
FROM
input_table
|
>
CALL
tvf1
(
arg1
)
|
>
CALL
tvf2
(
arg2
,
arg3
);
ORDER BY
pipe operator
|> ORDER BY expression [ 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
(
SELECT
1
AS
x
UNION
ALL
SELECT
3
AS
x
UNION
ALL
SELECT
2
AS
x
)
|
>
ORDER
BY
x
DESC
;
/*---+
| x |
+---+
| 3 |
| 2 |
| 1 |
+---*/
LIMIT
pipe operator
|> LIMIT count [ OFFSET skip_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'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
ORDER
BY
item
|
>
LIMIT
1
;
/*---------+-------+
| item | sales |
+---------+-------+
| apples | 2 |
+---------+-------*/
(
SELECT
'apples'
AS
item
,
2
AS
sales
UNION
ALL
SELECT
'bananas'
AS
item
,
5
AS
sales
UNION
ALL
SELECT
'carrots'
AS
item
,
8
AS
sales
)
|
>
ORDER
BY
item
|
>
LIMIT
1
OFFSET
2
;
/*---------+-------+
| 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
...
UNION
ALL
SELECT
1
UNION
ALL
SELECT
2
;
-- Pipe syntax
SELECT
*
FROM
...
|
>
UNION
ALL
(
SELECT
1
),
(
SELECT
2
);
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
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
]
)
AS
number
|
>
UNION
ALL
(
SELECT
1
);
/*--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
| 1 |
+--------*/
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
]
)
AS
number
|
>
UNION
DISTINCT
(
SELECT
1
);
/*--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
+--------*/
The following example shows multiple input queries to the right of the pipe operator:
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
]
)
AS
number
|
>
UNION
DISTINCT
(
SELECT
1
),
(
SELECT
2
);
/*--------+
| 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.
SELECT
1
AS
one_digit
,
10
AS
two_digit
|
>
UNION
ALL
BY
NAME
(
SELECT
20
AS
two_digit
,
2
AS
one_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.
SELECT
1
AS
one_digit
,
10
AS
two_digit
|
>
UNION
ALL
(
SELECT
20
AS
two_digit
,
2
AS
one_digit
);
-- Results follow column order from queries and ignore column names.
/*-----------+-----------+
| one_digit | two_digit |
+-----------+-----------+
| 1 | 10 |
| 20 | 2 |
+-----------+-----------*/
INTERSECT
pipe operator
query |> INTERSECT DISTINCT ( 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
...
INTERSECT
DISTINCT
SELECT
1
INTERSECT
DISTINCT
SELECT
2
;
-- Pipe syntax
SELECT
*
FROM
...
|
>
INTERSECT
DISTINCT
(
SELECT
1
),
(
SELECT
2
);
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
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
,
3
,
4
]
)
AS
number
|
>
INTERSECT
DISTINCT
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
2
,
3
,
3
,
5
]
)
AS
number
);
/*--------+
| number |
+--------+
| 2 |
| 3 |
+--------*/
The following example shows multiple input queries to the right of the pipe operator:
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
,
3
,
4
]
)
AS
number
|
>
INTERSECT
DISTINCT
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
2
,
3
,
3
,
5
]
)
AS
number
),
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
3
,
3
,
4
,
5
]
)
AS
number
);
/*--------+
| 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
NumbersTable
AS
(
SELECT
1
AS
one_digit
,
10
AS
two_digit
UNION
ALL
SELECT
2
,
20
UNION
ALL
SELECT
3
,
30
)
SELECT
one_digit
,
two_digit
FROM
NumbersTable
|
>
INTERSECT
DISTINCT
BY
NAME
(
SELECT
10
AS
two_digit
,
1
AS
one_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
NumbersTable
AS
(
SELECT
1
AS
one_digit
,
10
AS
two_digit
UNION
ALL
SELECT
2
,
20
UNION
ALL
SELECT
3
,
30
)
SELECT
one_digit
,
two_digit
FROM
NumbersTable
|
>
INTERSECT
DISTINCT
(
SELECT
10
AS
two_digit
,
1
AS
one_digit
);
-- No intersecting values detected because columns aren't recognized as the same.
/*-----------+-----------+
+-----------+-----------*/
EXCEPT
pipe operator
query |> EXCEPT DISTINCT ( 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
...
EXCEPT
DISTINCT
SELECT
1
EXCEPT
DISTINCT
SELECT
2
;
-- Pipe syntax
SELECT
*
FROM
...
|
>
EXCEPT
DISTINCT
(
SELECT
1
),
(
SELECT
2
);
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
...
EXCEPT
DISTINCT
SELECT
1
)
EXCEPT
DISTINCT
SELECT
2
;
-- Modified operation grouping
SELECT
*
FROM
...
EXCEPT
DISTINCT
(
SELECT
1
EXCEPT
DISTINCT
SELECT
2
);
-- Same modified operation grouping in pipe syntax
SELECT
*
FROM
...
|
>
EXCEPT
DISTINCT
(
SELECT
1
|
>
EXCEPT
DISTINCT
(
SELECT
2
)
);
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
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
,
3
,
4
]
)
AS
number
|
>
EXCEPT
DISTINCT
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
]
)
AS
number
);
/*--------+
| number |
+--------+
| 3 |
| 4 |
+--------*/
The following example shows multiple input queries to the right of the pipe operator:
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
,
3
,
4
]
)
AS
number
|
>
EXCEPT
DISTINCT
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
]
)
AS
number
),
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
4
]
)
AS
number
);
/*--------+
| 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
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
,
3
,
3
,
4
]
)
AS
number
|
>
EXCEPT
DISTINCT
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
2
]
)
AS
number
|
>
EXCEPT
DISTINCT
(
SELECT
*
FROM
UNNEST
(
ARRAY<INT64>
[
1
,
4
]
)
AS
number
)
);
/*--------+
| 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
NumbersTable
AS
(
SELECT
1
AS
one_digit
,
10
AS
two_digit
UNION
ALL
SELECT
2
,
20
UNION
ALL
SELECT
3
,
30
)
SELECT
one_digit
,
two_digit
FROM
NumbersTable
|
>
EXCEPT
DISTINCT
BY
NAME
(
SELECT
10
AS
two_digit
,
1
AS
one_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
NumbersTable
AS
(
SELECT
1
AS
one_digit
,
10
AS
two_digit
UNION
ALL
SELECT
2
,
20
UNION
ALL
SELECT
3
,
30
)
SELECT
one_digit
,
two_digit
FROM
NumbersTable
|
>
EXCEPT
DISTINCT
(
SELECT
10
AS
two_digit
,
1
AS
one_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
|> TABLESAMPLE SYSTEM ( percent PERCENT )
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
:
FROM
LargeTable
|
>
TABLESAMPLE
SYSTEM
(
1
PERCENT
);
WITH
pipe operator
|> WITH alias AS query , ...
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
SELECT
1
AS
key
|
>
WITH
t
AS
(
SELECT
1
AS
key
,
'my_value'
AS
value
)
|
>
INNER
JOIN
t
USING
(
key
)
/*---------+---------+
| key | value |
+---------+---------+
| 1 | my_value|
+---------+---------*/
SELECT
1
AS
key
-- Define multiple CTEs.
|
>
WITH
t1
AS
(
SELECT
2
),
t2
AS
(
SELECT
3
)
|
>
UNION
ALL
(
FROM
t1
),
(
FROM
t2
)
/*-----+
| key |
+-----+
| 1 |
| 2 |
| 3 |
+-----*/
The pipe WITH
operator allows a trailing comma:
SELECT
1
AS
key
|
>
WITH
t1
AS
(
SELECT
2
),
t2
AS
(
SELECT
3
),
|
>
UNION
ALL
(
FROM
t1
),
(
FROM
t2
)
/*-----+
| key |
+-----+
| 1 |
| 2 |
| 3 |
+-----*/
PIVOT
pipe operator
|> PIVOT ( aggregate_expression FOR input_column IN ( 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"
AS
product
,
51
AS
sales
,
"Q1"
AS
quarter
UNION
ALL
SELECT
"kale"
AS
product
,
4
AS
sales
,
"Q1"
AS
quarter
UNION
ALL
SELECT
"kale"
AS
product
,
45
AS
sales
,
"Q2"
AS
quarter
UNION
ALL
SELECT
"apple"
AS
product
,
8
AS
sales
,
"Q1"
AS
quarter
UNION
ALL
SELECT
"apple"
AS
product
,
10
AS
sales
,
"Q2"
AS
quarter
)
|
>
PIVOT
(
SUM
(
sales
)
FOR
quarter
IN
(
'Q1'
,
'Q2'
));
/*---------+----+------+
| product | Q1 | Q2 |
+---------+-----------+
| kale | 55 | 45 |
| apple | 8 | 10 |
+---------+----+------*/
UNPIVOT
pipe operator
|> UNPIVOT ( values_column FOR name_column IN ( 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'
as
product
,
55
AS
Q1
,
45
AS
Q2
UNION
ALL
SELECT
'apple'
,
8
,
10
)
|
>
UNPIVOT
(
sales
FOR
quarter
IN
(
Q1
,
Q2
));
/*---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| kale | 55 | Q1 |
| kale | 45 | Q2 |
| apple | 8 | Q1 |
| apple | 10 | Q2 |
+---------+-------+---------*/
MATCH_RECOGNIZE
pipe operator
|> MATCH_RECOGNIZE ( [ PARTITION BY partition_expr [, ... ] ] ORDER BY order_expr [ { ASC | DESC } ] [ { NULLS FIRST | NULLS LAST } ] [, ...] MEASURES { measures_expr [ AS ] alias } [, ... ] [ AFTER MATCH SKIP { PAST LAST ROW | TO NEXT ROW } ] PATTERN ( pattern ) DEFINE symbol AS boolean_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
(
SELECT
1
as
x
UNION
ALL
SELECT
2
UNION
ALL
SELECT
3
)
|
>
MATCH_RECOGNIZE
(
ORDER
BY
x
MEASURES
ARRAY_AGG
(
high
.
x
)
AS
high_agg
,
ARRAY_AGG
(
low
.
x
)
AS
low_agg
AFTER
MATCH
SKIP
TO
NEXT
ROW
PATTERN
(
low
|
high
)
DEFINE
low
AS
x
< =
2
,
high
AS
x
> =
2
);
/*----------+---------+
| high_agg | low_agg |
+----------+---------+
| NULL | [1] |
| NULL | [2] |
| [3] | NULL |
+----------+---------*/
