Differences between "IS NULL" and "ISNULL()" in Mysql

Question

Is there any difference in performance between the operator IS NULL and the function ISNULL()?

Pretty much the same, I think. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html — Amadan, Aug 20 '10 at 10:50

score 15 · Answer 1 · edited May 23 '17 at 12:10

15

This thread is similar, though not exactly on MySQL. According to the test shown there:

IS NULL is more efficient as it doesn't require a scan.

Seek is generally faster than a scan as it only includes qualifying records, while scan includes every row. It is explained in more detail here.

Another difference (though it's not performance) is their negation syntax:

IS NOT NULL  /* using NOT operator */
! ISNULL()  /* using exclamation mark */

edited May 23 '17 at 12:10

Community

answered Jul 09 '13 at 18:20

Geo

Thanks for making a concise summary! – Sebastian Neira Jul 09 '13 at 18:23
3

Newbie warning; this is only more efficient if the column is indexed :) – Ja͢ck Apr 21 '14 at 09:00
@Ja͢ck, If it isn't indexed which would you use? – Pacerier May 04 '15 at 10:26
2

The thread you're referring to is about MSSQL where `isnull` is not the same as MySQL `isnull` but the same as MySQL `ifnull`. So the answer overall is incorrect and misleading. – Sam Dark Nov 19 '15 at 23:22
`NOT ISNULL()` also valid statement – Vivek Puurkayastha Aug 12 '19 at 09:23

score 10 · Accepted Answer · edited Jul 09 '13 at 17:52

10

Looking into the MySQL manual, they seem to be synonyms really.

and even if they aren't, I would tend to trust the query optimizer to pick the best solution.

edited Jul 09 '13 at 17:52

Geo

answered Aug 20 '10 at 10:51

Pekka

2

I readed that too, but the ISNULL doc says that SHARES some special behaviors with IS NULL, that scared me. – Wiliam Aug 20 '10 at 10:54
@Wiliam good point. Maybe doing a test run is indeed the best way to go! – Pekka Aug 20 '10 at 10:55
5

Huh? In my understanding, "shares special behaviour" just means they're weird, but they're weird in the *same way*. – Amadan Aug 25 '10 at 18:37
@Wiliam, `isnull()` seems like a function, whereas `is null` is surely a "language feature". – Pacerier May 04 '15 at 10:28

2 Answers2