DON'T USE NOT IN

Sometimes learning the inner details is not a waste of time, but actually, in some conditions, it can save you a lot!

Look at this query:

*SELECT s.name
FROM students s
WHERE s.name <> Null
*

What do you think the returned table?

The returned table could be always an empty table!

Let me tell you why:

If you have a programming background, we can say the following:

NOT IN => !=
IN => ==

This happens because SQL can't compare values to NULL using = or <> any comparison with NULL returns UNKNOWN, not True or False.
This same trap silently breaks NOT IN queries, and here's how.

To understand why the previous query returned an empty table, let's say we have these tables:

And we do the following query:

*SELECT s.name
FROM students s
WHERE s.id NOT IN (
SELECT e.student_id
FROM enrollments e
)
*
We are basically asking to get the name of the students who are not enrolled into any course

So... what gives?

The nested query will return a table of students_ids
So let's say it returns (101E, 102E, 403E, Null)

What WHERE will do with our condition WHERE s.id NOT IN is the following:

It will compare s.id with the result in the following way:

s.id != 101E AND s.id != 102E AND ...... s.id != Null

And here is the biggie, we cannot use comparison operators with Null values, remember in python, when we want to check if a value in an array is null or not we do value is not None, we don't do value != Null or else it will throw an error.

Keep in mind that WHERE returns the table only when the condition returns True, but in our case there s.id != Null will return Unknown, and this will lead the second query to return False, so basically the result is an empty table even though we might have found students who are not enrolled.

The recap:

Do not use NOT IN and use NOT EXIST instead

NOT EXISTS is safe because it operates at the row level, but instead, it never compares values at all, so NULL never enters the picture.

SELECT s.name
FROM students s
WHERE NOT EXISTS (
SELECT 1
FROM enrollments e
WHERE e.student_id = s.id
);

When we used to use NOT IN you basically say "I want the students name who their names don't match any others in this list"

WHERE returns the table when the condition is True

If you insist to use NOT IN, then use Where e.student_id IS NOT NULL

SELECT s.name
FROM students s
WHERE s.id NOT IN (
SELECT e.student_id
FROM enrollments e
WHERE e.student_id IS NOT NULL
)

Thank you for reading

Happy querying!