BigQuery SQL Syntax Guide
Welcome!
In today’s post, we’ll explore some of the most commonly used BigQuery SQL syntax. I’ve curated this guide to be concise, easy to understand, and quick to read — perfect for anyone working with BigQuery on a day-to-day basis.
Let’s dive in!
Select Statements
SELECT
The SELECT list defines the columns that the query will return. Expressions in the SELECT list can refer to columns in any of the from_items in its corresponding FROM clause.
SELECT fruit FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);
SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);
/*-------+-----------*
| fruit | vegetable |
+-------+-----------+
| apple | carrot |
*-------+-----------*/
SELECT EXCEPT
A SELECT * EXCEPT statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;
/*-----------+----------*
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
*-----------+----------*/SELECT REPLACE
A SELECT * REPLACE statement specifies one or more expression AS identifier clauses. Each identifier must match a column name from the SELECT * statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;
/*----------+-----------+----------*
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | sprocket | 100 |
*----------+-----------+----------*/SELECT DISTINCT
A SELECT DISTINCT statement discards duplicate rows and returns only the remaining rows.
WITH PlayerStats AS (
SELECT ['Coolidge', 'Adams'] as Name, 3 as PointsScored UNION ALL
SELECT ['Adams', 'Buchanan'], 0 UNION ALL
SELECT ['Coolidge', 'Adams'], 1 UNION ALL
SELECT ['Kiran', 'Noam'], 1)
SELECT DISTINCT Name
FROM PlayerStats;
/*------------------+
| Name |
+------------------+
| [Coolidge,Adams] |
| [Adams,Buchanan] |
| [Kiran,Noam] |
+------------------*/SELECT ALL
A SELECT ALL statement returns all rows, including duplicate rows. SELECT ALL is the default behavior of SELECT.
SELECT AS STRUCT
This produces a value table with a STRUCT row type, where the STRUCT field names and types match the column names and types produced in the SELECT list.
SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)FROM Clause
The FROM clause indicates the table or tables from which to retrieve rows, and specifies how to join those rows together to produce a single stream of rows for processing in the rest of the query.
SELECT * FROM Roster;
SELECT * FROM dataset.Roster;
SELECT * FROM project.dataset.Roster;In the FROM clause, field_path is any path that resolves to a field within a data type. field_path can go arbitrarily deep into a nested data structure.
SELECT * FROM T1 t1, t1.array_column;
SELECT * FROM T1 t1, t1.struct_column.array_field;
SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;
SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;
SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;UNNEST Operator
The UNNEST operator takes an array and returns a table with one row for each element in the array. The output of UNNEST is one value table column. For these ARRAY element types, SELECT * against the value table column returns multiple columns:
SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;
/*---------+--------*
| numbers | offset |
+---------+--------+
| 10 | 0 |
| 20 | 1 |
| 30 | 2 |
*---------+--------*/UNNEST and Struct
For an input array of structs, UNNEST returns a row for each struct, with a separate column for each field in the struct. The alias for each column is the name of the corresponding struct field.
SELECT *
FROM UNNEST(
ARRAY<
STRUCT<
x INT64,
y STRING,
z STRUCT<a INT64, b INT64>>>[
(1, 'foo', (10, 11)),
(3, 'bar', (20, 21))]);
/*---+-----+----------*
| x | y | z |
+---+-----+----------+
| 1 | foo | {10, 11} |
| 3 | bar | {20, 21} |
*---+-----+----------*/Explicit UNNEST
WITH Coordinates AS (SELECT [1,2] AS position)
SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS results;Implicit UNNEST
WITH Coordinates AS (SELECT [1,2] AS position)
SELECT results FROM Coordinates, Coordinates.position AS results;UNNEST and NULL Values
UNNEST treats NULL values as follows:
NULLand empty arrays produce zero rows.- An array containing
NULLvalues produces rows containingNULLvalues.
PIVOT Operator
The PIVOT operator rotates rows into columns, using aggregation. PIVOT is part of the FROM clause.
WITH Produce AS (
SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
SELECT 'Apple', 1, 'Q1', 2021)
SELECT * FROM Produce
/*---------+-------+---------+------*
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale | 51 | Q1 | 2020 |
| Kale | 23 | Q2 | 2020 |
| Kale | 45 | Q3 | 2020 |
| Kale | 3 | Q4 | 2020 |
| Kale | 70 | Q1 | 2021 |
| Kale | 85 | Q2 | 2021 |
| Apple | 77 | Q1 | 2020 |
| Apple | 0 | Q2 | 2020 |
| Apple | 1 | Q1 | 2021 |
*---------+-------+---------+------*/
SELECT * FROM
Produce
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
/*---------+------+----+------+------+------*
| product | year | Q1 | Q2 | Q3 | Q4 |
+---------+------+----+------+------+------+
| Apple | 2020 | 77 | 0 | NULL | NULL |
| Apple | 2021 | 1 | NULL | NULL | NULL |
| Kale | 2020 | 51 | 23 | 45 | 3 |
| Kale | 2021 | 70 | 85 | NULL | NULL |
*---------+------+----+------+------+------*/
-- If you don't include year, then SUM is grouped only by product.
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
/*---------+-----+-----+------+------*
| product | Q1 | Q2 | Q3 | Q4 |
+---------+-----+-----+------+------+
| Apple | 78 | 0 | NULL | NULL |
| Kale | 121 | 108 | 45 | 3 |
*---------+-----+-----+------+------*/You can include multiple aggregation functions in the PIVOT. In this case, you must specify an alias for each aggregation. These aliases are used to construct the column names in the resulting table.
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) AS total_sales, COUNT(*) AS num_records FOR quarter IN ('Q1', 'Q2'))
/*--------+----------------+----------------+----------------+----------------*
|product | total_sales_Q1 | num_records_Q1 | total_sales_Q2 | num_records_Q2 |
+--------+----------------+----------------+----------------+----------------+
| Kale | 121 | 2 | 108 | 2 |
| Apple | 78 | 2 | 0 | 1 |
*--------+----------------+----------------+----------------+----------------*/UNPIVOT Operator
The UNPIVOT operator rotates columns into rows. UNPIVOT is part of the FROM clause.
WITH Produce AS (
SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce
/*---------+----+----+----+----*
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale | 51 | 23 | 45 | 3 |
| Apple | 77 | 0 | 25 | 2 |
*---------+----+----+----+----*/
SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
/*---------+-------+---------*
| product | sales | quarter |
+---------+-------+---------+
| Kale | 51 | Q1 |
| Kale | 23 | Q2 |
| Kale | 45 | Q3 |
| Kale | 3 | Q4 |
| Apple | 77 | Q1 |
| Apple | 0 | Q2 |
| Apple | 25 | Q3 |
| Apple | 2 | Q4 |
*---------+-------+---------*/In this example, we UNPIVOT four quarters into two semesters. This is a multi-column unpivot operation.
SELECT * FROM Produce
UNPIVOT(
(first_half_sales, second_half_sales)
FOR semesters
IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))
/*---------+------------------+-------------------+------------*
| product | first_half_sales | second_half_sales | semesters |
+---------+------------------+-------------------+------------+
| Kale | 51 | 23 | semester_1 |
| Kale | 45 | 3 | semester_2 |
| Apple | 77 | 0 | semester_1 |
| Apple | 25 | 2 | semester_2 |
*---------+------------------+-------------------+------------*/TABLESAMPLE operator
You can use the TABLESAMPLE operator to select a random sample of a dataset. This operator is useful when you're working with tables that have large amounts of data and you don't need precise answers.
SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)JOIN Operation
[Inner] JOIN
An INNER JOIN, or simply JOIN, effectively calculates the Cartesian product of the two from_items and discards all rows that don't meet the join condition.
FROM A INNER JOIN B ON A.w = B.y
/*
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
+---------------+
*/
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
/*---------------------------*
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
*---------------------------*/CROSS JOIN
CROSS JOIN returns the Cartesian product of the two from_items. In other words, it combines each row from the first from_item with each row from the second from_item.
FROM A CROSS JOIN B
/*
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
*/
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;
/*---------------------------*
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Adams | Knights |
| Adams | Lakers |
| Adams | Mustangs |
| Buchanan | Jaguars |
| Buchanan | Knights |
| Buchanan | Lakers |
| Buchanan | Mustangs |
| ... |
*---------------------------*/Comma cross JOIN (,)
CROSS JOIN can be written implicitly with a comma.
FROM A, B
/*
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
*/
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;
/*---------------------------*
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Adams | Knights |
| Adams | Lakers |
| Adams | Mustangs |
| Buchanan | Jaguars |
| Buchanan | Knights |
| Buchanan | Lakers |
| Buchanan | Mustangs |
| ... |
*---------------------------*/FULL [OUTER] JOIN
A FULL OUTER JOIN (or simply FULL JOIN) returns all fields for all matching rows in both from_items that meet the join condition. If a given row from one from_item doesn't join to any row in the other from_item, the row returns with NULL values for all columns from the other from_item.
FROM A FULL OUTER JOIN B ON A.w = B.y
/*
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
*/
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
/*---------------------------*
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
| NULL | Mustangs |
*---------------------------*/LEFT [OUTER] JOIN
The result of a LEFT OUTER JOIN (or simply LEFT JOIN) for two from_items always retains all rows of the left from_item in the JOIN operation, even if no rows in the right from_item satisfy the join predicate.
FROM A LEFT OUTER JOIN B ON A.w = B.y
/*
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
+---------------------------+
*/
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
/*---------------------------*
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
*---------------------------*/RIGHT [OUTER] JOIN
The result of a RIGHT OUTER JOIN (or simply RIGHT JOIN) for two from_items always retains all rows of the right from_item in the JOIN operation, even if no rows in the left from_item satisfy the join predicate.
FROM A RIGHT OUTER JOIN B ON A.w = B.y
/*
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
*/
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
/*---------------------------*
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| NULL | Mustangs |
*---------------------------*/JOIN operations in a sequence
The FROM clause can contain multiple JOIN operations in a sequence. JOINs are bound from left to right. For example:
FROM A JOIN B USING (x) JOIN C USING (x)
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
-- parantheses to group JOIN's
FROM ( (A JOIN B USING (x)) JOIN C USING (x) )
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
-- parantheses to group JOIN's in different order
FROM ( A JOIN (B JOIN C USING (x)) USING (x) )
-- B JOIN C USING (x) = result_1
-- A JOIN result_1 = result_2
-- result_2 = return valueWHERE Caluse
The WHERE clause filters the results of the FROM clause.
Get Atul Sutar’s stories in your inbox
Join Medium for free to get updates from this writer.
Only rows whose bool_expression evaluates to TRUE are included. Rows whose bool_expression evaluates to NULL or FALSE are discarded.
The evaluation of a query with a WHERE clause is typically completed in this order:
FROMWHEREGROUP BYand aggregationHAVINGWINDOWQUALIFYDISTINCTORDER BYLIMIT
SELECT * FROM Roster
WHERE SchoolID = 52;
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");GROUP BY Clause
The GROUP BY clause groups together rows in a table that share common values for certain columns. For a group of rows in the source table with non-distinct values, the GROUP BY clause aggregates them into a single combined row. This clause is commonly used when aggregate functions are present in the SELECT list, or to eliminate redundancy in the output.
WITH PlayerStats AS (
SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
SELECT 'Buchanan', 'Jie', 0 UNION ALL
SELECT 'Coolidge', 'Kiran', 1 UNION ALL
SELECT 'Adams', 'Noam', 4 UNION ALL
SELECT 'Buchanan', 'Jie', 13)
SELECT SUM(PointsScored) AS total_points, LastName
FROM PlayerStats
GROUP BY LastName;
/*--------------+----------+
| total_points | LastName |
+--------------+----------+
| 7 | Adams |
| 13 | Buchanan |
| 1 | Coolidge |
+--------------+----------*/
WITH
TeamStats AS (
SELECT
ARRAY<STRUCT<last_name STRING, first_name STRING, age INT64>>[
('Adams', 'Noam', 20), ('Buchanan', 'Jie', 19)] AS Team,
3 AS PointsScored
UNION ALL
SELECT [('Coolidge', 'Kiran', 21), ('Yang', 'Jason', 22)], 4
UNION ALL
SELECT [('Adams', 'Noam', 20), ('Buchanan', 'Jie', 19)], 10
UNION ALL
SELECT [('Coolidge', 'Kiran', 21), ('Yang', 'Jason', 22)], 7
)
SELECT
SUM(PointsScored) AS total_points,
Team
FROM TeamStats
GROUP BY Team;
/*--------------+--------------------------+
| total_points | teams |
+--------------+--------------------------+
| 13 | [{ |
| | last_name: "Adams", |
| | first_name: "Noam", |
| | age: 20 |
| | },{ |
| | last_name: "Buchanan",|
| | first_name: "Jie", |
| | age: 19 |
| | }] |
+-----------------------------------------+
| 11 | [{ |
| | last_name: "Coolidge",|
| | first_name: "Kiran", |
| | age: 21 |
| | },{ |
| | last_name: "Yang", |
| | first_name: "Jason", |
| | age: 22 |
| | }] |
+--------------+--------------------------*/GROUP BY ALL
The GROUP BY ALL clause groups rows by inferring grouping keys from the SELECT items.
WITH PlayerStats AS (
SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
SELECT 'Buchanan', 'Jie', 0 UNION ALL
SELECT 'Coolidge', 'Kiran', 1 UNION ALL
SELECT 'Adams', 'Noam', 4 UNION ALL
SELECT 'Buchanan', 'Jie', 13)
SELECT
SUM(PointsScored) AS total_points,
FirstName AS first_name,
LastName AS last_name
FROM PlayerStats
GROUP BY ALL;
/*--------------+------------+-----------+
| total_points | first_name | last_name |
+--------------+------------+-----------+
| 7 | Noam | Adams |
| 13 | Jie | Buchanan |
| 1 | Kiran | Coolidge |
+--------------+------------+-----------*/HAVING Clause
The HAVING clause filters the results produced by GROUP BY or aggregation. GROUP BY or aggregation must be present in the query. If aggregation is present, the HAVING clause is evaluated once for every aggregated row in the result set.
SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;Mandatory aggregation
Aggregation doesn’t have to be present in the HAVING clause itself, but aggregation must be present in at least one of the following forms:
SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;
SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;ORDER BY Clause
The ORDER BY clause specifies a column or expression as the sort criterion for the result set. If an ORDER BY clause isn't present, the order of the results of a query isn't defined.
Optional Clauses
NULLS FIRST | NULLS LAST:NULLS FIRST: Sort null values before non-null values.NULLS LAST. Sort null values after non-null values.ASC | DESC: Sort the results in ascending or descending order ofexpressionvalues.ASCis the default value. If null ordering isn't specified withNULLS FIRSTorNULLS LAST:NULLS FIRSTis applied by default if the sort order is ascending.NULLS LASTis applied by default if the sort order is descending.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x;
/*------+-------*
| x | y |
+------+-------+
| NULL | false |
| 1 | true |
| 9 | true |
*------+-------*/
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x NULLS LAST;
/*------+-------*
| x | y |
+------+-------+
| 1 | true |
| 9 | true |
| NULL | false |
*------+-------*/
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x DESC;
/*------+-------*
| x | y |
+------+-------+
| 9 | true |
| 1 | true |
| NULL | false |
*------+-------*/
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
/*------+-------*
| x | y |
+------+-------+
| NULL | false |
| 9 | true |
| 1 | true |
*------+-------*/QUALIFY Clause
The QUALIFY clause filters the results of window functions. A window function is required to be present in the QUALIFY clause or the SELECT list.
Only rows whose bool_expression evaluates to TRUE are included. Rows whose bool_expression evaluates to NULL or FALSE are discarded.
SELECT
item,
RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3
/*---------+------*
| item | rank |
+---------+------+
| kale | 1 |
| lettuce | 2 |
| cabbage | 3 |
*---------+------*/
SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3
/*---------*
| item |
+---------+
| kale |
| lettuce |
| cabbage |
*---------*/SET Operators
Set operators combine or filter results from two or more input queries into a single result set.
UNION
The UNION operator returns the combined results of the left and right input queries. Columns are matched according to the rules described previously and rows are concatenated vertically.
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
UNION DISTINCT
SELECT 2;
/*--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
+--------*/
SELECT 1 AS one_digit, 10 AS two_digit
UNION ALL
SELECT 20 AS two_digit, 2 AS one_digit;
-- Column values are matched by position and not column name.
/*-----------+-----------+
| one_digit | two_digit |
+-----------+-----------+
| 1 | 10 |
| 20 | 2 |
+-----------+-----------*/
SELECT 1 AS one_digit, 10 AS two_digit
UNION ALL BY NAME
SELECT 20 AS two_digit, 2 AS one_digit;
-- Column values now match.
/*-----------+-----------+
| one_digit | two_digit |
+-----------+-----------+
| 1 | 10 |
| 2 | 20 |
+-----------+-----------*/
INTERSECT
The INTERSECT operator returns rows that are found in the results of both the left and right input queries.
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 |
+--------*/
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
INTERSECT DISTINCT
SELECT * FROM UNNEST(ARRAY<INT64>[3, 3, 4, 5]) AS number;
/*--------+
| number |
+--------+
| 3 |
+--------*/
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.
/*-----------+-----------+
+-----------+-----------*/
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 |
+-----------+-----------*/EXCEPT
The EXCEPT operator returns rows from the left input query that aren't present in the right input query.
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 |
+--------*/
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 |
+--------+
| 3 |
+--------*/LIMIT and OFFSET Clause
Limits the number of rows to return in a query. Optionally includes the ability to skip over rows.
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2;
/*---------*
| letter |
+---------+
| a |
| b |
*---------*/
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1;
/*---------*
| letter |
+---------+
| b |
| c |
| d |
*---------*/WITH Clause
A WITH clause contains one or more common table expressions (CTEs). A CTE acts like a temporary table that you can reference within a single query expression.
In this example, a WITH clause defines two non-recursive CTEs that are referenced in the related set operation, where one CTE is referenced by each of the set operation's input query expressions:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2You can break up more complex queries into a WITH clause and WITH SELECT statement instead of writing nested table subqueries. For example:
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)
