What is the difference between the = and <=> operators?

Q: What is the difference between the = and operators?

Both `=` and ` ` are comparison operators in MySQL, but they behave differently when dealing with NULL values. `=` checks if two values are equal. If **either value is NULL**, the result is **NULL** (unknown). Cannot compare NULL values reliably. SELECT 5 = 5; -- 1 (TRUE) SELECT 5 = 3; -- 0 (FALSE) SELECT NULL = NULL; -- NULL (not TRUE) ` ` is MySQL's **NULL-safe** equality operator. Returns **1 (TRUE)** only when the two values are equal — including when both are NULL. Returns **0 (FALSE)** when not equal. SELECT 5 5; -- 1 SELECT 5 3; -- 0 SELECT NULL NULL; -- 1 (NULL-safe) `=` returns NULL when comparing to NULL; ` ` returns 1 if both sides are NULL. `=` is a standard equality operator; ` ` is specifically designed for NULL-safe comparisons. ` ` is often used in conditions where NULL values must be treated as valid comparisons (e.g., WHERE column variable). In short: use ` ` when you want reliable comparisons that include NULL values; use `=` for normal equality checks.

Difficulty: 5/10

equality operators, null handling, SQL semantics

Difference Between = and <=> Operators in MySQL

Both = and <=> are comparison operators in MySQL, but they behave differently when dealing with NULL values.

1. = (Equality Operator)

1
= checks if two values are equal.
2
If either value is NULL, the result is NULL (unknown).
3
Cannot compare NULL values reliably.

Examples of =

2. <=> (NULL-Safe Equality Operator)

1
<=> is MySQL's NULL-safe equality operator.
2
Returns 1 (TRUE) only when the two values are equal — including when both are NULL.
3
Returns 0 (FALSE) when not equal.

Examples of <=>

3. Key Differences

1
= returns NULL when comparing to NULL; <=> returns 1 if both sides are NULL.
2
= is a standard equality operator; <=> is specifically designed for NULL-safe comparisons.
3
<=> is often used in conditions where NULL values must be treated as valid comparisons (e.g., WHERE column <=> variable).

In short: use <=> when you want reliable comparisons that include NULL values; use = for normal equality checks.

Scenario Questions

0-2 years experience

2-5 years experience

1A bug surfaced where `WHERE col1 = col2` is dropping rows when both columns are NULL. How would you modify the query to fix this?

Follow-up Questions