CODEWITHSUNDEEP
google-bigquery
Ev Dolzhenko
Ev Dolzhenko

Reputation: 6232

BigQuery COUNT(*) ... WHERE NOT (...) returns confusing results

I have a table with device column which could be 'P', 'T' or NULL, but the condition like device NOT IN('P', 'T') seems to miss the rows with NULL. Can be demonstrated with following queries

$ bq query 'SELECT count(*) FROM t'
+----------+
|   f0_    |
+----------+
| 29542063 |
+----------+
$ bq query "SELECT count(*) FROM t WHERE device = 'P' OR device = 'T'"
+---------+
|   f0_   |
+---------+
| 8268436 |
+---------+
$ bq query "SELECT count(*) FROM t WHERE NOT (device = 'P' OR device = 'T')"
+-----+
| f0_ |
+-----+
|   0 |
+-----+
$ bq query "SELECT count(*) FROM t WHERE device IS NULL"
+----------+
|   f0_    |
+----------+
| 21273627 |
+----------+

Why the query with WHERE NOT returns 0 while IS NULL one returns correct result?

Upvotes: 1

Views: 220

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

"Why the query with WHERE NOT returns 0 while IS NULL one returns correct result?"

Because 

SELECT null != 'anything'

returns 'null', not 'false'.

Upvotes: 3

Related Questions