Re: Need efficient way to do comparison with NULL as an option

D. Dante Lorenso wrote:
> I'm looking for an operator that will compare NULL with NULL and
> evaluate as TRUE.
> I have a BIGINT column which might contain NULL values.  I want to pass
> a value to compare with that column in my WHERE clause.  If the value
> I'm comparing is 0, I want it to match the NULL values.  Here is a
> sample query that I currently use:
>   SELECT *
>   FROM mytable
>   WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;
> The '?' placeholders used in the query will receive the same value which
> might be any one of the following: NULL, 0, 1, 2, 3, etc.
> What I'd really like is an operator that will compare NULL with NULL and
> evaluate as TRUE.  Does that exist?

Is this the answer?

   SELECT *
   FROM mytable
   WHERE col IS NOT DISTINCT FROM NULLIF(?, 0);

-- Dante