24

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

Improve this question
1

2 Answers 2

Reset to default
17

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 */
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Newbie warning; this is only more efficient if the column is indexed :)
@Ja͢ck, If it isn't indexed which would you use?
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.
NOT ISNULL() also valid statement
11

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.

Improve this answer

4 Comments

I readed that too, but the ISNULL doc says that SHARES some special behaviors with IS NULL, that scared me.
@Wiliam good point. Maybe doing a test run is indeed the best way to go!
Huh? In my understanding, "shares special behaviour" just means they're weird, but they're weird in the same way.
@Wiliam, isnull() seems like a function, whereas is null is surely a "language feature".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.