PostgreSQL how to treat null as a value

Question

Let us say:

1. I have a table with the name "movie" and it contains a field with the name "categoryid" which points to another "category" table.

2. The field "categoryid" can be null in case no category was selected for a movie. Otherwise it's a number.

3. I have a user interface for a movies search page, that lists all categories in the form of a list of checkboxes so the user can select multiple categories. And this list of checkboxes also includes an "Uncategorised" option to select movies where "categoryid" is null

Now the user is now selecting null and multiple other checkboxes, this reflects into an SQL code that would look something like this:

SELECT * FROM movie WHERE categoryid in (1, 5, 9, NULL);

Apparently, the above code doesn't work. I guess null is not treated as a value is SQL like it is in other languages! Uncategorized movies were filtered out. The below code works however

SELECT * FROM movie WHERE categoryid IN (1, 5, 9) OR categoryid IS NULL;

But unfortunately, the framework I work with can't make this exception, and I have no flexibility over the structure of the database as it's generated by another software. So, is there any way I can use the NULL value with the IN operator? or any other operator that will accept a list of allowed values?

Answer 1

My recommendation is not to use NULL for this. NULL works best if you interpret it as “unknown”, but in your case the value is known: it is “uncategorized”.

So it would be best if you create a special category for that and handle it like all others. Don't allow NULLs here, and everything becomes simple.

Alternatively, you can replace NULL with a value that doesn't otherwise exist in the query using something like

coalesce(category, '')

That would create the “uncategorized” category on the fly.

Answer 2

IN does not work with NULL values unfortunately. An alternative would be to put the values in a derived table, and use IS NOT DISTINCT FROM:

select m.*
from movie m
inner join (values (1), (5), (9), (null)) v(val) 
    on v.val is not distinct from m.category_id

It might be more convenient for your application to pass the list of values as an array:

select m.*
from movie m
inner join unnest(array[1, 5, 9, null]) v(val)
    on v.val is not distinct from m.category_id

Answer 3

Another approach, with same results would be:

SELECT * FROM movie WHERE zeroifnull(categoryid) in (1, 5, 9, 0);

The idea behind is previously transform your NULL value to another value (zero in this case).

You can also use IFNULL().