Comparison operators in MySQL are used to compare two values and return TRUE (1), FALSE (0), or NULL (unknown). When NULL values are involved, comparisons behave differently because NULL represents an unknown value.

1. Common Comparison Operators

1
= (equal to)
2
!= or <> (not equal to)
3
> (greater than)
4
< (less than)
5
>= (greater or equal)
6
<= (less or equal)
7
BETWEEN
8
IN / NOT IN
9
LIKE

Normally, comparison operators return TRUE or FALSE — except when NULL is involved.

2. How NULL Affects Comparisons

1
Any comparison with NULL returns NULL (unknown).
2
This includes expressions like: 5 = NULL, NULL <> 10, NULL > 0.
3
Even NULL = NULL returns NULL (because NULL means unknown, so two unknowns cannot be equal).

Examples of NULL Comparisons

3. The NULL-Safe Equality Operator (<=>)

1
<=> is MySQL's NULL-safe comparison operator.
2
Returns 1 if both values are equal — including when both are NULL.
3
Returns 0 otherwise.

NULL-Safe Comparison Example

4. NULL with IN / NOT IN

1
IN ignores NULL values in the list.
2
NOT IN returns NULL if the list contains a NULL — causing unexpected results.

NULL with NOT IN

Key Points

1
NULL means unknown, so comparisons involving NULL result in NULL.
2
Use <=> for NULL-safe comparisons.
3
Beware of NULL inside NOT IN — it can make the whole condition NULL.
4
NULL does not behave like 0 or empty string.

In summary: MySQL's comparison operators work normally unless NULL is present. When NULL appears, the result becomes NULL unless you use the NULL-safe operator <=>.

Scenario Questions

0-2 years experience

1You need to write a query that returns all orders where the discount column is greater than 0. How would you write the WHERE clause, and what would happen if some rows have NULL in discount?
2If you run SELECT * FROM users WHERE last_login > '2023-01-01'; and some rows have NULL in last_login, which rows are returned and why?
3How would you use a comparison operator to find rows where price is not equal to 100, and what does MySQL return for rows where price is NULL?

2-5 years experience

1Our reporting feature stopped showing rows where the 'score' column is NULL after we added a condition score >= 0. Explain why those rows disappeared and how you would fix it.
2We have a query that uses LEFT JOIN and then filters on joined_table.value = 5 in the WHERE clause. It unexpectedly excludes rows with NULL values. Walk me through why and propose a change.
3During a data migration we need to replace NULLs with a sentinel value but also keep comparison semantics. How would you rewrite comparisons to work correctly after the migration?

5-8 years experience

1Design a large‑scale analytics pipeline that aggregates user activity stored in MySQL. Some columns are nullable and are used in range filters. Discuss how you would index and write predicates to avoid full table scans while handling NULL correctly.
2Our service builds dynamic WHERE clauses from user‑provided filters. Users can request 'age > 30' or leave age blank (NULL). How would you structure the query builder to safely handle NULL comparisons and prevent logic bugs?
3Explain the impact of using IS NULL versus = NULL in query plans and how it affects optimizer choices in a high‑throughput environment.

8+ years experience

1We are consolidating several legacy MySQL schemas into a unified data warehouse. Different teams use different conventions for missing data (NULL vs special codes). How would you define a company‑wide policy for comparison operators and NULL handling, and what migration steps would you enforce?
2Our microservice architecture shares a MySQL instance across services that have conflicting expectations about NULL semantics in joins and filters. Describe how you would design a shared data access layer or abstraction to ensure consistent behavior and minimize bugs.
3When designing a new feature that must support both MySQL and a NoSQL store, how would you abstract comparison logic so that NULL handling semantics remain consistent across the two storage engines?

Follow-up Questions

What happens if you write WHERE col = NULL in MySQL?
Why does MySQL use three‑valued logic for comparisons involving NULL?
How do IS NULL and IS NOT NULL differ from using = or <> with NULL?

Sharethis question

Share via WhatsApp, X, Facebook, LinkedIn or copy link. Open Graph preview enabled.