VR Startup Company (NOT IN clause)

[system9944020765]

Hi!
I have a question regarding the VR Startup Company
https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-multiple-tables-with-a-vr-startup

I type in the request to do the 4th exercise:
SELECT *
FROM projects
WHERE project_id NOT IN (
SELECT DISTINCT current_project
FROM employees
);

But I receive an empty result:(
I know how to fix this, I should add a “WHERE current_project IS NOT NULL” to the subquery. So the working version is:
SELECT *
FROM projects
WHERE project_id NOT IN (
SELECT DISTINCT current_project
FROM employees
WHERE current_project IS NOT NULL
);

The question is: why the first version did not work? I know it included an NULL value, but how did it eliminate projects with 8, 9, 10 IDs from the result? Or did it just break the query?

[mirja_t]

You find your question addressed in this Stack Overflow topic, where the 2nd answer seems the most valuable to me:

Since NULL is an unknown, a NOT IN query containing a NULL or NULL s in the list of possible values will always return 0 records since there is no way to be sure that the NULL value is not the value being tested.