Comparison Operators
How =, <>, <, > and <=> work in MySQL 8.4, and why every comparison returns 1, 0 or NULL rather than a boolean.
expr = expr -- also <>, !=, <, <=, >, >=, <=> ; result is 1, 0 or NULLA comparison operator asks a yes/no question about two values. MySQL has no separate boolean type, so the answer comes back as 1 (TRUE), 0 (FALSE), or NULL. A WHERE clause keeps a row only when its condition evaluates to 1 — both 0 and NULL discard it, and that is where most surprises start.
The operators
| Operator | Meaning |
|---|---|
= | equal |
<>, != | not equal — two spellings of the same operator |
< <= > >= | ordering comparisons |
<=> | NULL-safe equal |
Three-valued logic
Every operator above except <=> returns NULL when either side is NULL: the input is unknown, so the answer is unknown. 1 = NULL, 1 <> NULL and even NULL = NULL all produce NULL, never TRUE. <=> is the exception — it returns 1 when both operands are NULL and 0 when exactly one is. The manual describes it as equivalent to the standard SQL IS NOT DISTINCT FROM operator.
Types are coerced
Comparisons work on numbers and strings, and strings are converted to numbers and numbers to strings as needed. '10' = 10 is 1. So is 'abc' = 0, with a warning, because 'abc' converts to 0. Compare like with like, or make the conversion explicit with CAST().
Example
-- orders.status is nullable: rows that were never triaged have NULL.
-- Reads as "not cancelled", but silently drops untriaged orders:
-- NULL <> 'cancelled' is NULL, and WHERE only keeps TRUE.
SELECT id, status, total_cents
FROM orders
WHERE total_cents >= 5000
AND status <> 'cancelled';
-- What was actually meant: keep untriaged orders too.
SELECT id, status, total_cents
FROM orders
WHERE total_cents >= 5000
AND (status <> 'cancelled' OR status IS NULL);
-- Or say it once with the NULL-safe operator negated:
-- NOT (status <=> 'cancelled') is TRUE for NULL and for any other value.
SELECT id, status, total_cents
FROM orders
WHERE total_cents >= 5000
AND NOT (status <=> 'cancelled');When to use it
- Filter orders above a price threshold without accidentally dropping rows with a NULL status
- Compare two nullable columns for equality when detecting changed rows in an import
- Exclude one category value while keeping rows where the category was never set
- Debug a WHERE clause that returns fewer rows than expected after a nullable column was added
More examples
<> and != are the same; <=> is not
<> and != are interchangeable spellings, so you need to recognise both in other people's code. <=> is a different operator: it is the whole (a = b OR (a IS NULL AND b IS NULL)) expression collapsed into three characters. Reach for it whenever you compare two columns that are both nullable.
SELECT 1 <> 2, 1 != 2; -- 1, 1 (identical operators)
SELECT NULL = NULL, NULL <=> NULL; -- NULL, 1
SELECT 1 = NULL, 1 <=> NULL; -- NULL, 0
-- The longhand that <=> replaces:
SELECT (a = b OR (a IS NULL AND b IS NULL)) AS same_longhand,
(a <=> b) AS same_shorthand
FROM pairs;Implicit type conversion in comparisons
MySQL converts strings to numbers and numbers to strings as necessary, which quietly turns a typo into a match. This bites on legacy VARCHAR columns holding numeric identifiers; making the conversion explicit with CAST() makes the intent visible, at the cost of preventing index use on that column.
SELECT '10' = 10; -- 1: the string is converted to a number
SELECT 'abc' = 0; -- 1 plus a warning: 'abc' converts to 0
SELECT '10' = '10.0'; -- 0: both sides are strings, so it is a string compare
SELECT 10 = '10.0'; -- 1: one side is a number, so both compare as numbers
-- Safer when a VARCHAR column holds digits: convert on purpose.
SELECT * FROM legacy_rows WHERE CAST(ref_code AS UNSIGNED) = 42;Row comparison as shorthand for chained AND
Row constructors let you compare several columns in one expression, and the MySQL optimizer handles the two forms identically for equality. They are handy for composite-key lookups, but the manual warns against mixing row constructors with AND/OR and against range comparisons like (c2, c3) > (1, 1), which can stop the optimizer using the full index.
-- Shorthand: a row constructor on both sides.
SELECT * FROM people
WHERE (last_name, first_name) = ('Ng', 'Ada');
-- Longhand: exactly the same query to the optimizer.
SELECT * FROM people
WHERE last_name = 'Ng' AND first_name = 'Ada';
Discussion