Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 75 ยท Querying with MySQL

NULL Handling and Comparison Rules

Understand NULL in MySQL: three-valued logic, the null-safe <=> operator, IFNULL and COALESCE, and the implicit type conversion traps in comparisons.

NULL means "no value known", and it follows its own rules. NULL = NULL is not true, NOT IN with a NULL in the list can return nothing at all, and ORDER BY has to decide where the missing values go. A second source of surprises is comparison itself: MySQL quietly converts between strings, numbers and dates, and the conversions can make 'abc' = 0 true. Most "my query returns the wrong rows" bugs trace back to one of these two topics.

This lesson explains both with examples, so you can predict what a condition does before you run it.

NULL is not a value

Unknown NULL means the value is missing or unknown.
Never equal NULL = NULL is NULL, not true.
IS NULL The only test that finds missing values.

WHERE keeps a row only when the condition is TRUE.

1 / 2

Three-valued logic

Ordinary comparisons give TRUE or FALSE. Once NULL is involved there is a third result, UNKNOWN, which MySQL displays as NULL. Any comparison with NULL (=, <>, <, >) yields NULL, and a WHERE clause keeps a row only when the condition is TRUE. That means unknown rows vanish from both WHERE x = 5 and WHERE x <> 5.

The logical operators have their own truth tables:

a b a AND b a OR b
TRUE NULL NULL TRUE
FALSE NULL FALSE NULL
NULL NULL NULL NULL

NOT NULL is NULL. So FALSE AND anything is FALSE and TRUE OR anything is TRUE, but anything else mixed with NULL stays unknown.

SQL Comparisons with NULL are never true

Press Run (or Ctrl + Enter) to see the result here.

The first query prints NULL for the first, second and third columns, 0 for the fourth and 1 for the fifth. In the second block, assignee <> 'ana' returns only "Fix bug". The two unassigned tasks are neither equal to nor different from 'ana', so they are dropped. Add OR assignee IS NULL to include them. The last query shows that COUNT(column) skips NULLs (4 rows, 2 assigned).

You can also use IS TRUE, IS NOT TRUE, IS FALSE and IS UNKNOWN in MySQL. x IS NOT TRUE is true for both FALSE and NULL, which is sometimes the shortcut you want.

The NOT IN trap

x IN (1, 2, NULL) is true when x is 1 or 2, and NULL otherwise. x NOT IN (1, 2, NULL) is therefore never true: for any other x it evaluates to NULL. The same happens when the list comes from a subquery whose column contains a NULL:

SQL NOT IN returns nothing when the list has a NULL

Press Run (or Ctrl + Enter) to see the result here.

The first query returns no rows because of the single NULL. The second filters it out and returns Ben and Cara, and so does the third. NOT EXISTS is the safest form because it does not have this problem, so prefer it for "has no matching rows" checks (Subqueries, Derived Tables and LATERAL).

The null-safe equals operator

MySQL has an operator that treats NULL as an ordinary value: <=>. It returns 1 or 0, never NULL:

SQL
SELECT NULL <=> NULL;   -- 1
SELECT 1 <=> NULL;      -- 0
SELECT 1 <=> 1;         -- 1
SELECT NULL = NULL;     -- NULL

It is useful when you compare two columns that may both be empty, for instance to find changed values in a sync job or to join on optional keys:

SQL
SELECT * FROM old_rows AS o JOIN new_rows AS n ON o.phone <=> n.phone;
UPDATE contacts SET changed = 1 WHERE NOT (phone <=> new_phone);

<=> is MySQL syntax. The standard SQL spelling is IS NOT DISTINCT FROM, which MySQL does not support. SQLite offers the same idea as IS and IS NOT, which work between any two values, not only with the keyword NULL:

SQL Null-safe comparison in SQLite

Press Run (or Ctrl + Enter) to see the result here.

Row 3 compares two missing values: the plain = gives NULL but the null-safe form says they are equal (1). Rows 4 and 5 count as changed, which the plain comparison could not tell you.

An index note: in InnoDB a UNIQUE index allows many rows with NULL, since two unknowns are not considered equal. If "at most one row without a value" is a rule you need, enforce it another way, for example with a generated column (Generated Columns).

Replacing NULL

Three functions turn missing values into something usable:

SQL
SELECT IFNULL(nickname, 'anonymous');        -- exactly two arguments, MySQL-specific
SELECT COALESCE(mobile, home, work, 'n/a');  -- first non-NULL of any number of arguments, standard
SELECT NULLIF(discount, 0);                  -- NULL when equal, otherwise the value
SELECT IF(shipped_at IS NULL, 'pending', 'shipped');
  • ISNULL(expr) is a different function from the one in SQL Server. In MySQL it takes one argument and returns 1 when the value is NULL and 0 otherwise: ISNULL(NULL) is 1, ISNULL(0) and ISNULL('') are 0. It is the function form of IS NULL and does not substitute a default. Code written for SQL Server as ISNULL(a, 0) fails in MySQL with an argument-count error; use IFNULL(a, 0) or COALESCE(a, 0) instead.
  • COALESCE is the standard form and works in every database. Prefer it in portable code. IFNULL is a two-argument shortcut.
  • NULLIF(a, b) returns NULL when a = b. Its classic use is avoiding division by zero: total / NULLIF(count, 0) yields NULL instead of an error or a bad number.
  • Aggregates skip NULL: SUM, AVG, MIN, MAX. AVG divides by the number of non-NULL values, so wrap with COALESCE when a missing value should count as zero (GROUP BY, HAVING and Aggregates in MySQL).
  • Sorting: MySQL puts NULL first in ascending order and last in descending order. There is no NULLS LAST keyword, so write ORDER BY col IS NULL, col to push the empty ones to the end.
  • In string concatenation, CONCAT returns NULL if any part is NULL (Strings, Dates and Math Functions).
SQL COALESCE, NULLIF and sorting NULLs last

Press Run (or Ctrl + Enter) to see the result here.

Ben's per_visit is NULL because NULLIF(0, 0) turns the divisor into NULL. Dev, with unknown visits, sorts last because visits IS NULL is 1 for him and 0 for the others.

Implicit type conversion

When you compare values of different types, MySQL converts one side before comparing. The rules are mostly sensible, but a few are hazardous:

Comparison MySQL result Why
1 = '1' 1 the string becomes a number
'abc' = 0 1 'abc' converts to 0 (with a warning)
'5 apples' = 5 1 only the leading digits are read
'10' < '9' 1 both are strings: compared character by character
10 < '9' 0 one side is a number: compared as numbers
'1e3' = 1000 1 scientific notation is understood
0.1 + 0.2 = 0.3 1 exact decimal literals
0.1e0 + 0.2e0 = 0.3e0 0 approximate floating point

The big rules: if either side is a number, MySQL compares numerically. If both are strings, it compares text using the column's collation. If one side is a date or time and the other a string, the string is parsed as a date.

The practical dangers are:

  1. Strings that are not numbers match zero. WHERE code = 0 on a VARCHAR column matches every row whose code starts with a letter. Quote string literals: WHERE code = '0'.
  2. Leading digits only. '12abc' counts as 12, so an id typed with trailing junk can still match. In strict mode, INSERT rejects such values for numeric columns, but comparisons only warn.
  3. Index loss. Comparing a VARCHAR column with a number casts every row, so an index on the column cannot be used and the query scans the whole table. The reverse is harmless: an INT column compared with '42' converts the constant once (Query Optimization Techniques).
  4. Different character sets or collations on two columns can force a conversion in a join and block index use (Character Sets and Collations).

You can see the conversion behind the first two rows with SHOW WARNINGS right after the query: it reports "Truncated incorrect DOUBLE value: 'abc'".

SQLite decides differently: the column's type affinity says which side is converted, and a text value that is not a number never equals a number. Its arithmetic, however, shows the same "leading digits" idea, which makes a neat live illustration:

SQL Strings that look a bit like numbers

Press Run (or Ctrl + Enter) to see the result here.

In SQLite a TEXT column converts the number to text, so code = 0 matches only the row holding '0', the opposite direction from MySQL. The second-to-last query forces numeric conversion and matches three rows, because 'A1' + 0 is 0. That is the result MySQL gives for code = 0 on a text column by default. The two engines disagree on the rule, so the safe habit is the same everywhere: make both sides the same type yourself.

A checklist for correct comparisons

  • Test missing values with IS NULL or IS NOT NULL, never with = NULL.
  • Use <=> (MySQL) or IS (SQLite) when both sides may be NULL.
  • Prefer NOT EXISTS over NOT IN against subqueries that might contain NULL.
  • Use COALESCE for portable defaults and NULLIF to guard divisions.
  • Quote text literals, and keep columns and parameters the same type as the column they are compared with.
  • Never apply a cast or function to an indexed column in WHERE when you can convert the constant instead.

What ran live here

Three-valued logic, NOT IN against NULL, null-safe comparison (with SQLite's IS), COALESCE, NULLIF, IFNULL and sorting rules ran in SQLite. SQLite converts less aggressively than MySQL, so MySQL's 'abc' = 0 result, the <=> operator, IS UNKNOWN and SHOW WARNINGS output are described as read-only code or tables.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the expression NULL = NULL return in MySQL?

    Any comparison with NULL yields NULL (unknown). Use IS NULL, or the null-safe operator <=>, to compare missing values.
  2. 2. Why does WHERE id NOT IN (SELECT referred_by FROM referrals) return no rows when referred_by contains a NULL?

    For any id not in the list, the comparison with the NULL element is unknown, so the whole NOT IN is NULL and the row is dropped. NOT EXISTS avoids this.
  3. 3. What does 1 <=> NULL return?

    The null-safe operator never returns NULL. It returns 1 when both sides are equal or both NULL, and 0 otherwise.
  4. 4. In MySQL, which row does WHERE name_col = 0 match when name_col is a VARCHAR holding 'abc'?

    When one side is a number, MySQL compares numerically and converts the text, so non-numeric strings become 0. Quote the literal ('0') to avoid it.
  5. 5. How do you push NULL values to the end of an ascending sort in MySQL?

    MySQL has no NULLS LAST. Sorting first by col IS NULL (0 for values, 1 for NULL) and then by col puts the NULLs at the end.

Summary

  • Comparisons with NULL yield NULL (unknown), and WHERE keeps only TRUE rows. Test with IS NULL / IS NOT NULL.
  • AND/OR follow three-valued logic: FALSE AND NULL is FALSE, TRUE OR NULL is TRUE, everything else stays NULL.
  • NOT IN with a NULL in the list returns nothing. Use NOT EXISTS or filter the NULLs out.
  • <=> is MySQL's null-safe equality. COALESCE and NULLIF replace and guard values, and IFNULL is the two-argument shortcut.
  • MySQL converts types during comparison: non-numeric text equals 0 against a number, and only leading digits count. Quote literals and match types so indexes stay usable.

Next: INSERT, UPDATE and DELETE in MySQL.