Understanding MySQL NULL Comparison in Queries

During interviews the author frequently asks a seemingly simple MySQL question about NULL handling, and many candidates—including fresh graduates, mid‑level engineers, and senior architects—answer incorrectly, highlighting a widespread knowledge gap.

The scenario assumes a table test_null with two columns, id and name (VARCHAR, nullable), containing a single row where name is NULL.

First query: select * from test_null where name = 'john'; Because name is NULL, the comparison name = 'john' evaluates to NULL, which is treated as false in a WHERE clause, so no records are returned.

Second query: select * from test_null where name <> 'john'; Although many expect this to return the row (since NULL is not equal to 'john'), the expression name <> 'john' also yields NULL, and therefore the condition is not true, resulting in no rows returned as well.

The correct answer for both queries is that they return no records because NULL represents an unknown value; any comparison with NULL yields NULL, which does not satisfy the WHERE predicate. Understanding this behavior is essential for writing correct SQL and for database‑related programming tasks.

The author notes that if this article receives more than ten views, a deeper analysis of NULL values will be published.