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

Query

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

Query

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

Query

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

Result

Snowflake

Query

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

Result

WITH RECURSIVE cte

BigQuery

Query

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;

Result

Snowflake

Query

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;

Result

UNION ALL

BigQuery

Query

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

Result

Snowflake

Query

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

Result

UNION DISTINCT

BigQuery

Query

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

Result

Snowflake

Query

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

Result

INTERSECT

BigQuery

Query

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

Result

Snowflake

Query

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

Result

EXCEPT

BigQuery

Query

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

Result

Snowflake

Query

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

Result

ORDER BY

BigQuery

Query

SELECT * FROM test.exampleTable ORDER BY col1;

Result

Snowflake

Query

SELECT * FROM test.exampleTable ORDER BY col1;

Result

LIMIT

BigQuery

Query

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

Result

Snowflake

Query

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

Result

SELECT ALL

BigQuery

Query

SELECT ALL * FROM test.exampleTable ORDER BY 1;

Result

Snowflake

Query

SELECT ALL * FROM test.exampleTable ORDER BY 1;

Result

SELECT DISTINCT

BigQuery

Query

SELECT DISTINCT * FROM test.exampleTable ORDER BY 1;

Result

Snowflake

Query

SELECT DISTINCT * FROM test.exampleTable ORDER BY 1;

Result

WHERE

BigQuery

Query

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

Result

Snowflake

Query

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

Result

GROUP BY

BigQuery

Query

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

Result

Snowflake

Query

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

Result

HAVING

BigQuery

Query

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

Result

Snowflake

Query

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

Result

QUALIFY

BigQuery

Query

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

Result

Snowflake

Query

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

Result

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