Keeping track of how NULL values are handled in different SQL dialects can be tricky. This post will serve as a living document where I keep track of how BigQuery does this.

NULL and STRING comparison

Comparison always returns NULL.

SELECT
  CAST(NULL AS STRING) = '', -- null
  CAST(NULL AS STRING) = 'value', -- null
  CAST(NULL AS STRING) = CAST(NULL AS STRING) -- null

NULL and BOOL comparison

NULL is not TRUE or FALSE. It is NULL. Comparing NULL with = or <> is NULL.

SELECT 
    CAST(NULL AS BOOL) is TRUE, -- false
    CAST(NULL AS BOOL) is FALSE, -- false
    CAST(NULL AS BOOL) is NULL, -- true
    CAST(NULL AS BOOL) = TRUE, -- null
    CAST(NULL AS BOOL) = FALSE, -- null
    CAST(NULL AS BOOL) = CAST(NULL AS BOOL), -- null
    CAST(NULL AS BOOL) <> TRUE, -- null
    CAST(NULL AS BOOL) <> FALSE, -- null
    CAST(NULL AS BOOL) <> CAST(NULL AS BOOL), -- null

NULL and BOOL operators

NULL and FALSE is False, and NULL or TRUE is True.

SELECT
  CAST(NULL AS BOOL) AND TRUE, -- null
  CAST(NULL AS BOOL) AND FALSE, -- false
  CAST(NULL AS BOOL) OR TRUE, -- true
  CAST(NULL AS BOOL) OR FALSE, -- null
  NOT CAST(NULL AS BOOL) -- null

NULL and NUMERIC operations

According to the docs:

All mathematical functions have the following behaviors:

  • They return NULL if any of the input parameters is NULL.
  • They return NaN if any of the arguments is NaN.

Which I can confirm:

SELECT 
  1 + CAST(NULL AS INT64), -- null
  1 - CAST(NULL AS INT64), -- null
  1 / CAST(NULL AS INT64), -- null
  1 * CAST(NULL AS INT64), -- null
  POW(1, CAST(NULL AS INT64)), -- null
  ABS(CAST(NULL AS INT64)), -- null
  1.1 + CAST(NULL AS FLOAT64), -- null
  1.1 - CAST(NULL AS FLOAT64), -- null
  1.1 / CAST(NULL AS FLOAT64), -- null
  1.1 * CAST(NULL AS FLOAT64), -- null
  POW(1.1, CAST(NULL AS FLOAT64)), -- null
  ABS(CAST(NULL AS FLOAT64)), -- null

NULL and NUMERIC comparison

Directly comparing returns NULL:

WITH a AS (SELECT NULL AS n)
SELECT
  1 < n, -- null
  1 > n, -- null
  1 = n, -- null
  1 <> n, -- null
  1.1 < n, -- null
  1.1 < n, -- null
FROM
  a

When ordering, NULL is the lowest value (below NaN and -infinity):

    SELECT
        n
    FROM UNNEST([
        1, 
        -1, 
        0, 
        CAST(NULL AS INT64), 
        CAST('NaN' AS FLOAT64), 
        CAST('inf' AS FLOAT64), 
        CAST('-inf' AS FLOAT64)]) AS n
    ORDER BY n

returns:

Row n
1 null
2 NaN
3 -Infinity
4 -1.0
5 0.0
6 1.0
7 Infinity

NULL and STRING operations

SELECT 
    ASCII(s), -- null
    BYTE_LENGTH(s), -- null
    CHAR_LENGTH(s), -- null
    CHR(NULL), -- null
FROM (
    SELECT CAST(NULL AS STRING) AS s
) value