I have came across the following article recently.

Software is a bit like this house plant: its wellbeing depends on the folks who constantly tend it. Without us around, it gradually erodes.

I used Google Orkut in the past and I loved it at that time. It was fun to send messages in the scrapbook, cool usernames, and enjoy all the other social media features that felt cool back then. But after the rise of Facebook and other giants, Google shut down Orkut. They sent us an email with a link to download our data archive and said goodbye. I still have those emails from Orkut, and when I read them recently, they gave me a nostalgic happiness.

What I realize now is that everything eventually comes to an end when the gardeners stop tending their gardens. We cannot take it for granted forever. Today we have access to some of the most amazing things in human history. We need to be grateful and enjoy the beauty of it. So keep on gardening, and cherish your fellow gardeners.

Article link

SELECT *
FROM TABLE1 
WHERE ID NOT IN (
    SELECT ID
    FROM TABLE2
)

When I ran the above query I know the condition will give me the results, because the data exists in the TABLE1 which is not in TABLE2. But I didn’t go any results. I got surprised. After I a while I found out the issue.

So the reason is that ID column in the TABLE2 have null values. If any value of ID in the subquery is NULL, then the entire NOT IN clause fails to match anything. This is standard SQL behavior because NULL makes the whole comparison unknown.

Solution:

1. You can use NOT NULL condition in the sub-query as below:

SELECT *
FROM TABLE1 
WHERE ID NOT IN (
    SELECT ID
    FROM TABLE2
    WHERE ID IS NOT NULL
)

2. Use NOT EXISTS:

SELECT *
FROM TABLE1 T1
WHERE NOT EXISTS (
    SELECT 1
    FROM TABLE2 T2
    WHERE T1.ID = T2.ID
)

The NOT EXISTS will automatically handle null values and safer to use.

References

StackOverflow, Geeks for geeks