BigQuery - filtering without losing zero values

Question

I am trying to filter the database, but unfortunately I am losing the "null" values ​​anyway:

The sample looks like

Name | City | Sold Nike | NYC | 15 null | SFO | 20 Mega | SEA | 10 null | null | 8 nike | CHI | 12 

I try to search for data without Nike in any way, but when I go for

 Select ... where not lower(Name) contains "nike" 

but then I only find

Mega | SEA | 10

and all rows with null values ​​for Name also disappeared. How can I prevent them from being deleted? Thanks

Answer 1

See this:

 select * from (select string(NULL) as name,'SFO' as city, 20 as sold), (select 'Nike' as name,'NYC' as city, 15 as sold), where not lower(name) contains 'nike2' or name is null 

returns

 +-----+------+------+------+---+ | Row | name | city | sold | | +-----+------+------+------+---+ | 1 | null | SFO | 20 | | | 2 | Nike | NYC | 15 | | +-----+------+------+------+---+