SQL Queries

SQL Queries

InMemoryEmulator.BigQuery implements a GoogleSQL parser and execution engine. This page documents the supported SQL syntax.

SELECT

SELECT * FROM dataset.table
SELECT col1, col2 FROM dataset.table
SELECT col1 AS alias FROM dataset.table
SELECT DISTINCT col1 FROM dataset.table
SELECT * EXCEPT (col1) FROM dataset.table
SELECT * REPLACE (col1 * 2 AS col1) FROM dataset.table
SELECT AS STRUCT col1, col2 FROM dataset.table

WHERE

SELECT * FROM dataset.table WHERE col1 = 'value'
SELECT * FROM dataset.table WHERE col1 > 10 AND col2 IS NOT NULL
SELECT * FROM dataset.table WHERE col1 IN ('a', 'b', 'c')
SELECT * FROM dataset.table WHERE col1 BETWEEN 1 AND 10
SELECT * FROM dataset.table WHERE col1 LIKE 'prefix%'

ORDER BY / LIMIT / OFFSET

SELECT * FROM dataset.table ORDER BY col1 ASC, col2 DESC
SELECT * FROM dataset.table ORDER BY col1 NULLS FIRST
SELECT * FROM dataset.table LIMIT 10 OFFSET 20

JOIN

SELECT * FROM dataset.t1 INNER JOIN dataset.t2 ON t1.id = t2.id
SELECT * FROM dataset.t1 LEFT JOIN dataset.t2 ON t1.id = t2.id
SELECT * FROM dataset.t1 RIGHT JOIN dataset.t2 ON t1.id = t2.id
SELECT * FROM dataset.t1 FULL OUTER JOIN dataset.t2 ON t1.id = t2.id
SELECT * FROM dataset.t1 CROSS JOIN dataset.t2

GROUP BY / HAVING

SELECT col1, COUNT(*) FROM dataset.table GROUP BY col1
SELECT col1, SUM(col2) FROM dataset.table GROUP BY col1 HAVING SUM(col2) > 100
SELECT col1, col2, SUM(col3) FROM dataset.table GROUP BY ROLLUP(col1, col2)

WITH (CTEs)

WITH cte AS (
  SELECT col1, col2 FROM dataset.table WHERE col1 > 10
)
SELECT * FROM cte

Recursive CTEs are also supported:

WITH RECURSIVE cte AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte

UNNEST

SELECT * FROM UNNEST([1, 2, 3]) AS val
SELECT val, off FROM UNNEST([10, 20, 30]) AS val WITH OFFSET AS off

QUALIFY

SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS rn
FROM dataset.table
QUALIFY rn = 1

PIVOT / UNPIVOT

SELECT * FROM dataset.table
PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

SELECT * FROM dataset.wide_table
UNPIVOT (value FOR quarter IN (q1, q2, q3, q4))

TABLESAMPLE

SELECT * FROM dataset.table TABLESAMPLE SYSTEM (10 PERCENT)

WINDOW Clause

SELECT col1, SUM(col2) OVER w
FROM dataset.table
WINDOW w AS (PARTITION BY col1 ORDER BY col2)

Set Operations

SELECT col1 FROM dataset.t1
UNION ALL
SELECT col1 FROM dataset.t2

SELECT col1 FROM dataset.t1
INTERSECT DISTINCT
SELECT col1 FROM dataset.t2

SELECT col1 FROM dataset.t1
EXCEPT DISTINCT
SELECT col1 FROM dataset.t2

Wildcard Tables

SELECT * FROM `dataset.table_*`
WHERE _TABLE_SUFFIX BETWEEN '2020' AND '2023'

Aggregate Functions

Function	Description
COUNT(*)	Count all rows
COUNT(col)	Count non-NULL values
COUNT(DISTINCT col)	Count distinct non-NULL values
SUM(col)	Sum of values
AVG(col)	Average of values
MIN(col)	Minimum value
MAX(col)	Maximum value
ANY_VALUE(col)	Any non-NULL value from the group
ARRAY_AGG(col)	Array of values
STRING_AGG(col, sep)	Concatenated string of values
COUNTIF(condition)	Count where condition is true
LOGICAL_AND(col)	True if all values are true
LOGICAL_OR(col)	True if any value is true
STDDEV(col)	Sample standard deviation (alias of STDDEV_SAMP)
STDDEV_SAMP(col)	Sample (unbiased) standard deviation
STDDEV_POP(col)	Population (biased) standard deviation
VARIANCE(col)	Sample variance (alias of VAR_SAMP)
VAR_SAMP(col)	Sample (unbiased) variance
VAR_POP(col)	Population (biased) variance

Built-in Functions

See the GoogleSQL function reference for full documentation. The emulator implements 325+ functions across these categories:

• String (50): CONCAT, LENGTH, LOWER, UPPER, TRIM, SUBSTR, REPLACE, STARTS_WITH, ENDS_WITH, REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_REPLACE, REGEXP_EXTRACT_ALL, REGEXP_INSTR, REGEXP_SUBSTR, LEFT, RIGHT, LPAD, RPAD, LTRIM, RTRIM, REPEAT, REVERSE, FORMAT, SPLIT, STRPOS, INSTR, INITCAP, SOUNDEX, TRANSLATE, UNICODE, ASCII, CHR, BYTE_LENGTH, CHAR_LENGTH, OCTET_LENGTH, CONTAINS_SUBSTR, NORMALIZE, NORMALIZE_AND_CASEFOLD, SAFE_CONVERT_BYTES_TO_STRING, COLLATE, CODE_POINTS_TO_BYTES, CODE_POINTS_TO_STRING, TO_CODE_POINTS, TO_BASE32, FROM_BASE32, TO_BASE64, FROM_BASE64, TO_HEX, FROM_HEX
• Math (39): ABS, CEIL, FLOOR, ROUND, TRUNC, MOD, POW, SQRT, LOG, LN, LOG10, EXP, SIGN, DIV, GREATEST, LEAST, RAND, RANGE_BUCKET, IEEE_DIVIDE, IS_INF, IS_NAN, SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE, SAFE_NEGATE, SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, SINH, COSH, TANH, ASINH, ACOSH, ATANH
• Date (12): CURRENT_DATE, DATE, DATE_ADD, DATE_SUB, DATE_DIFF, DATE_TRUNC, DATE_FROM_UNIX_DATE, EXTRACT, FORMAT_DATE, PARSE_DATE, LAST_DAY, UNIX_DATE
• Datetime (10): CURRENT_DATETIME, DATETIME, DATETIME_ADD, DATETIME_SUB, DATETIME_DIFF, DATETIME_TRUNC, EXTRACT, FORMAT_DATETIME, LAST_DAY, PARSE_DATETIME
• Time (9): CURRENT_TIME, TIME, TIME_ADD, TIME_SUB, TIME_DIFF, TIME_TRUNC, EXTRACT, FORMAT_TIME, PARSE_TIME
• Timestamp (16): CURRENT_TIMESTAMP, TIMESTAMP, TIMESTAMP_ADD, TIMESTAMP_SUB, TIMESTAMP_DIFF, TIMESTAMP_TRUNC, TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS, EXTRACT, FORMAT_TIMESTAMP, PARSE_TIMESTAMP, STRING, UNIX_SECONDS, UNIX_MILLIS, UNIX_MICROS
• Interval (5): MAKE_INTERVAL, EXTRACT, JUSTIFY_DAYS, JUSTIFY_HOURS, JUSTIFY_INTERVAL
• Aggregate (15): COUNT, SUM, AVG, MIN, MAX, ANY_VALUE, ARRAY_AGG, ARRAY_CONCAT_AGG, STRING_AGG, COUNTIF, LOGICAL_AND, LOGICAL_OR, BIT_AND, BIT_OR, BIT_XOR
• Statistical Aggregate (7): STDDEV, STDDEV_SAMP, STDDEV_POP, VARIANCE, VAR_SAMP, VAR_POP, CORR, COVAR_POP, COVAR_SAMP
• Approximate Aggregate (4): APPROX_COUNT_DISTINCT, APPROX_QUANTILES, APPROX_TOP_COUNT, APPROX_TOP_SUM (all computed exactly in-memory)
• HLL++ (4): HLL_COUNT.INIT, HLL_COUNT.MERGE, HLL_COUNT.MERGE_PARTIAL, HLL_COUNT.EXTRACT (exact counting in-memory)
• Window / Numbering (7): ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, Aggregate OVER
• Navigation (7): FIRST_VALUE, LAST_VALUE, NTH_VALUE, LAG, LEAD, PERCENTILE_CONT, PERCENTILE_DISC
• Array (20): ARRAY_LENGTH, ARRAY_CONCAT, ARRAY_REVERSE, ARRAY_TO_STRING, ARRAY_FIRST, ARRAY_LAST, ARRAY_SLICE, GENERATE_ARRAY, GENERATE_DATE_ARRAY, GENERATE_TIMESTAMP_ARRAY, ARRAY_FILTER, ARRAY_TRANSFORM, ARRAY_INCLUDES, ARRAY_INCLUDES_ALL, ARRAY_INCLUDES_ANY, ARRAY_IS_DISTINCT, ARRAY_MAX, ARRAY_MIN, ARRAY_SUM, ARRAY_AVG
• JSON (25): JSON_EXTRACT, JSON_EXTRACT_SCALAR, JSON_VALUE, JSON_QUERY, JSON_EXTRACT_ARRAY, JSON_QUERY_ARRAY, JSON_EXTRACT_STRING_ARRAY, JSON_VALUE_ARRAY, JSON_KEYS, JSON_SET, JSON_STRIP_NULLS, JSON_TYPE, JSON_ARRAY, JSON_OBJECT, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_CONTAINS, PARSE_JSON, TO_JSON, TO_JSON_STRING, BOOL/INT64/FLOAT64/STRING (JSON→SQL), LAX_BOOL, LAX_INT64, LAX_FLOAT64, LAX_STRING
• Hash (5): MD5, SHA1, SHA256, SHA512, FARM_FINGERPRINT
• Bit (1): BIT_COUNT
• Net (10): NET.HOST, NET.PUBLIC_SUFFIX, NET.REG_DOMAIN, NET.IP_FROM_STRING, NET.IP_TO_STRING, NET.IP_NET_MASK, NET.IP_TRUNC, NET.IPV4_FROM_INT64, NET.IPV4_TO_INT64, NET.SAFE_IP_FROM_STRING
• Conversion (4): CAST, SAFE_CAST, PARSE_NUMERIC, PARSE_BIGNUMERIC
• Conditional / Utility (8): IF, IIF, IFNULL, NULLIF, COALESCE, CASE, ERROR, GENERATE_UUID
• Geography (41): ST_GEOGPOINT, ST_DISTANCE, ST_CONTAINS, ST_WITHIN, ST_INTERSECTS, ST_AREA, ST_LENGTH, and 34 more (see Features — Geography Functions)
• Range (6): RANGE, RANGE_START, RANGE_END, RANGE_CONTAINS, RANGE_OVERLAPS, GENERATE_RANGE_ARRAY
• Vector / Distance (6): COSINE_DISTANCE, EUCLIDEAN_DISTANCE, DOT_PRODUCT, APPROX_COSINE_DISTANCE, APPROX_EUCLIDEAN_DISTANCE, APPROX_DOT_PRODUCT
• AEAD Encryption (13): KEYS.NEW_KEYSET, KEYS.ADD_KEY_FROM_RAW_BYTES, KEYS.KEYSET_CHAIN, KEYS.KEYSET_FROM_JSON, KEYS.KEYSET_TO_JSON, KEYS.ROTATE_KEYSET, KEYS.KEYSET_LENGTH, AEAD.ENCRYPT, AEAD.DECRYPT_BYTES, AEAD.DECRYPT_STRING, DETERMINISTIC_ENCRYPT, DETERMINISTIC_DECRYPT_BYTES, DETERMINISTIC_DECRYPT_STRING
• Security / Debug (2): SESSION_USER, ERROR

Parameterised Queries

var results = await client.ExecuteQueryAsync(
    "SELECT * FROM dataset.table WHERE id = @id AND name = @name",
    new[]
    {
        new BigQueryParameter("id", BigQueryDbType.Int64, 42),
        new BigQueryParameter("name", BigQueryDbType.String, "Alice"),
    });