Comparing NULL with Comparison Operators in BigQuery

In BigQuery, if either side of a comparison operator is NULL, the result is NULL. Both of the following queries return NULL.

SELECT CAST(NULL AS INT64) < 1;
SELECT CAST(NULL AS INT64) = CAST(NULL AS INT64);

In the example below, there are two rows — one with NULL and one with today's date — but when filtering with a WHERE clause to retrieve only today's date, the row where the value is NULL is not returned.

WITH temp_table AS (
  SELECT CAST(NULL AS DATETIME) AS created_at
  UNION ALL SELECT CAST(CURRENT_DATE() AS DATETIME)
)
SELECT * FROM temp_table
WHERE created_at = CURRENT_DATE();