SELECT

Translation specification for the SELECT statement in BigQuery

Description

The select statement scans one or more tables or expressions and returns the computed result rows (Query syntax BigQuery documentation)

Syntax

[ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ]
  { select | ( query_expr ) | set_operation }
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ LIMIT count [ OFFSET skip_rows ] ]

select:
  SELECT
    [ WITH differential_privacy_clause ]
    [ { ALL | DISTINCT } ]
    [ AS { STRUCT | VALUE } ]
    select_list
  [ FROM from_clause[, ...] ]
  [ WHERE bool_expression ]
  [ GROUP BY group_by_specification ]
  [ HAVING bool_expression ]
  [ QUALIFY bool_expression ]
  [ WINDOW window_clause ]
  
select_list:
  { select_all | select_expression } [, ...]

select_all:
  [ expression. ]*
  [ EXCEPT ( column_name [, ...] ) ]
  [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

select_expression:
  expression [ [ AS ] alias ]

Sample Source Patterns

Setup data

BigQuery

CREATE TABLE test.exampleTable
(
	col1 INTEGER,
	col2 STRING(10),
);

INSERT INTO test.exampleTable VALUES (11, 'A');
INSERT INTO test.exampleTable VALUES (4, 'C');
INSERT INTO test.exampleTable VALUES (6, 'B');
INSERT INTO test.exampleTable VALUES (15, 'D');
INSERT INTO test.exampleTable VALUES (8, 'D');
INSERT INTO test.exampleTable VALUES (9, 'A');
INSERT INTO test.exampleTable VALUES (10, 'B');
INSERT INTO test.exampleTable VALUES (3, 'B');

CREATE TABLE test.otherTable
(
  myCol1 INTEGER
);

INSERT INTO test.otherTable VALUES (11);

INSERT INTO test.otherTable VALUES (8);

Snowflake

CREATE TABLE test.exampleTable
(
	col1 INTEGER,
	col2 VARCHAR(10),
);

INSERT INTO test.exampleTable VALUES (11, 'A');
INSERT INTO test.exampleTable VALUES (4, 'C');
INSERT INTO test.exampleTable VALUES (6, 'B');
INSERT INTO test.exampleTable VALUES (15, 'D');
INSERT INTO test.exampleTable VALUES (8, 'D');
INSERT INTO test.exampleTable VALUES (9, 'A');
INSERT INTO test.exampleTable VALUES (10, 'B');
INSERT INTO test.exampleTable VALUES (3, 'B');

CREATE TABLE test.otherTable
(
  myCol1 INTEGER
);

INSERT INTO test.otherTable VALUES (11);

INSERT INTO test.otherTable VALUES (8);

WITH cte

BigQuery

WITH myCte AS
(
  SELECT 'Hello ' || col2 as col2_concat FROM test.exampleTable
)
SELECT col2_concat FROM myCte ORDER BY 1;

Snowflake

WITH myCte AS
(
  SELECT 'Hello ' || col2 as col2_concat FROM test.exampleTable
)
SELECT col2_concat FROM myCte ORDER BY 1;

WITH RECURSIVE cte

BigQuery

WITH RECURSIVE fiboCte AS
(
  (SELECT 0 AS n1, 1 AS n2) UNION ALL (SELECT n2 AS n1, n1 + n2 AS n2 FROM fiboCte WHERE n1 < 5)
)
SELECT n2 FROM fiboCte ORDER BY 1;

Snowflake

WITH RECURSIVE fiboCte AS
(
  (SELECT 0 AS n1, 1 AS n2) UNION ALL (SELECT n2 AS n1, n1 + n2 AS n2 FROM fiboCte WHERE n1 < 5)
)
SELECT n2 FROM fiboCte ORDER BY 1;

UNION ALL

BigQuery

SELECT col1 FROM test.exampleTable
UNION ALL
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

Snowflake

SELECT col1 FROM test.exampleTable
UNION ALL
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

UNION DISTINCT

BigQuery

SELECT col1 FROM test.exampleTable
UNION DISTINCT
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

Snowflake

SELECT col1 FROM test.exampleTable
UNION DISTINCT
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

INTERSECT

BigQuery

SELECT col1 FROM test.exampleTable
INTERSECT DISTINCT
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

Snowflake

SELECT col1 FROM test.exampleTable
INTERSECT
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

EXCEPT

BigQuery

SELECT col1 FROM test.exampleTable
EXCEPT DISTINCT
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

Snowflake

SELECT col1 FROM test.exampleTable
EXCEPT
SELECT myCol1 FROM test.otherTable
ORDER BY 1;

ORDER BY

BigQuery

SELECT * FROM test.exampleTable ORDER BY col1;

Snowflake

SELECT * FROM test.exampleTable ORDER BY col1;

LIMIT

BigQuery

SELECT * FROM test.exampleTable ORDER BY 1 LIMIT 4 OFFSET 2;

Snowflake

SELECT * FROM test.exampleTable ORDER BY 1 LIMIT 4 OFFSET 2;

SELECT ALL

BigQuery

SELECT ALL * FROM test.exampleTable ORDER BY 1;

Snowflake

SELECT ALL * FROM test.exampleTable ORDER BY 1;

SELECT DISTINCT

BigQuery

SELECT DISTINCT * FROM test.exampleTable ORDER BY 1;

Snowflake

SELECT DISTINCT * FROM test.exampleTable ORDER BY 1;

WHERE

BigQuery

SELECT * FROM test.exampleTable WHERE col1 < 10 ORDER BY 1;

Snowflake

SELECT * FROM test.exampleTable WHERE col1 < 10 ORDER BY 1;

GROUP BY

BigQuery

SELECT SUM(col1), col2 FROM test.exampleTable GROUP BY ROLLUP(col2, col1) ORDER BY 1;

Snowflake

SELECT SUM(col1), col2 FROM test.exampleTable GROUP BY ROLLUP(col2, col1) ORDER BY 1;

HAVING

BigQuery

SELECT col2 FROM exampleTable GROUP BY col2 HAVING SUM(col1) > 20;

Snowflake

SELECT col2 FROM exampleTable GROUP BY col2 HAVING SUM(col1) > 20;

QUALIFY

BigQuery

SELECT SUM(col1) OVER(ORDER BY col2) AS col1_sum FROM test.exampleTable QUALIFY col1_sum > 20 ORDER BY 1;

Snowflake

SELECT SUM(col1) OVER(ORDER BY col2) AS col1_sum FROM test.exampleTable QUALIFY col1_sum > 20 ORDER BY 1;

Known Issues

1. Differential privacy clause not supported

BigQuery allows applying differential privacy over some statistical functions to introduce noise in the data, difficulting the subtraction of information about individuals when analyzing the query results.

Snowflake does not support applying differential privacy to the results of a SELECT query, the user will be warned about this everytime the clause is found.

2. WINDOW clause not supported

Snowflake does not support declaring window variables to use it in window functions, the user will be warned about this everytime the clause is found.

Related EWIs

  1. MSC-BQ0006: Snowflake does not support differential privacy

  2. MSC-BQ0007: Snowflake does not support named windows

PreviousStatementsNextFROM

Last updated