Search code examples
sqlgoogle-bigquerywhere-clause

SQL where clause with multiple criteria in BigQuery?

Struggling a bit with this seemingly easy SQL query. How can I query a table so that it returns values that are either NULL OR after a certain date? Here's what I have so far:

SELECT *
FROM data 
AND Operational_End_Date IN (NULL, > '2022-08-01')

Thanks!

Solution

  • Just use OR:

    select *
from data 
where operational_end_date is null 
   or operational_end_date > date '2022-08-01'

    Note: assuming that operational_end_date is of the date datatype as its name seems to imply, you would compare it against a literal date rather than a string.

    You could also phrase it with coalesce, as in:

    select *
from data 
where coalesce(operational_end_date, date '9999-12-31') > date '2022-08-01'

    The idea is to turn null values to a far future date (here, '9999-12-31' is the greatest date that BQ supports), so they pass the inequality condition.