Experience working with NULL in MySQL

Last week, I encountered a scenario that perfectly illustrates the quirks of working with NULL values in MySQL. I thought it might be beneficial to share my experience, as it could save someone else a bit of head-scratching.

We maintain two separate database instances—let’s call them instance1 and instance2. Instance2 was originally cloned from instance1, but since they power different web pages, their data has diverged over time.

During a routine check on our testing system, I ran the following queries to get a user count from each instance:

-- Query 1:
SELECT COUNT(*) FROM instance1.usr_users; -- Result: 152,563

-- Query 2:
SELECT COUNT(*) FROM instance2.usr_users; -- Result: 163,264

Curious about the overlap of users between the two systems, I executed another query:

-- Query 3:
SELECT COUNT(*) FROM instance1.usr_users WHERE email IN (SELECT email FROM instance2.usr_users); -- Result: 142,546

Next, I wanted to find out how many users from instance1 did not exist in instance2. This is where things got interesting:

-- Query 4:
SELECT COUNT(*) FROM instance1.usr_users WHERE email NOT IN (SELECT email FROM instance2.usr_users); -- Result: 0

This result was puzzling because, logically, there should have been some users unique to instance1. After some investigation, I realized that the presence of NULL values in the email column of instance2 was causing the NOT IN subquery to produce unexpected results. In SQL, NULL represents an unknown value, and any comparison with NULL yields NULL, which is considered as false in the context of a WHERE clause.

To address this issue, I modified the subquery to exclude NULL values:

-- Query 5:
SELECT COUNT(*) FROM instance1.usr_users WHERE email NOT IN (SELECT email FROM instance2.usr_users WHERE email IS NOT NULL);

With this correction, the query returned the expected count of users that were unique to instance1.

This experience was a reminder of how important it is to be mindful of NULL values in SQL queries. For those looking to deepen their understanding of working with NULL in MySQL, I recommend checking out the official MySQL documentation: Working with NULL Values.