The IN command question in SQL

There's a function called COALESCE that will return the first non-null in a list (of any size), or null if all of them are null. So you could write your query this way:

SELECT * FROM movies WHERE COALESCE(year_released, genre) IS NULL;

This would test if ALL of them are null. Now to test if ANY of them are null is trickier. Anything I can think of that would make the testing more compact would not be portable or clear.

Also remember that when using IN with a list, the list cannot include null.

Now depending on which SQL engine you use, there may be more compact ways to do the job, but they may have other drawbacks. For example. in Oracle you can say:

SELECT * FROM movies WHERE NVL2(title,1,0)+NVL2(year_released,1,0)+NVL2(genre,1,0) < 3;

...but that's not a whole lot shorter, it's much harder to understand, and certainly not portable to other SQL engines.

If this is something that you expect to use frequently, it might make sense to create a view, for example:

CREATE VIEW movies_with_missing_data AS
SELECT * FROM movies WHERE id IS NULL 
                        OR title IS NULL 
                        OR year_released IS NULL 
                        OR genre IS NULL;

And then, when you want to perform the actual query, you would just say this:

SELECT * FROM movies_with_missing_data;

And by the way, id is likely to be a primary key, and if so it cannot be null.