Search code examples
mysqlsqldatabasegroup-bysql-null

MySQL SELECT IF MAX: unexpected behaviour

I have a MySQL table with no records in it.

-- all of these return false
SELECT IF(MAX(id), 'true', 'false') FROM `vcr_grades`
SELECT IF(MAX(id) = NULL, 'true', 'false') FROM `vcr_grades`
SELECT IF(!MAX(id), 'true', 'false') FROM `vcr_grades`

-- returns NULL
SELECT MAX(id) FROM `vcr_grades`

AUTO_INCREMENT is set to another value than 1. Primary key is id.

What am I missing??

Solution

  • You apparently have zero rows.

    When you use an aggregate function like MAX() or COUNT(), the query will return at least one row, even if the table has zero rows.

    MAX(id) returns NULL if there are no non-NULL id values in the set of rows it scans. If you have zero rows, this will return NULL.

    NULL = NULL does not return true, it returns NULL.

    mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

    IF(NULL, 'true', 'false') returns 'false'.

    You might like to read about the null-safe equal operator.

    mysql> select null <=> null;
+---------------+
| null <=> null |
+---------------+
|             1 |
+---------------+