Menu
Is there any difference in performance between the operator IS NULL and the function ISNULL()?
504
The MySQL ISNULL () function is used to check for any NULL values in the expression passed to it as a parameter. If the expression has/results to NULL, it displays 1. If the expression does not have or result in NULL, the function returns 0. The MySQL IFNULL () function is used to return a specified value if the expression is NULL.
If the expression does not have or result in NULL, the function returns 0. The MySQL IFNULL () function is used to return a specified value if the expression is NULL.
Significantly both ISNULL () function and IS NULL operator do not have any difference and shares some common behaviors’. The only difference which we can see is in their syntax.
While you can always add a WHERE clause to check for NULL in a column of a table, MySQL provides you with additional ways to tackle this problem. The MySQL ISNULL () function is used to check for any NULL values in the expression passed to it as a parameter. If the expression has/results to NULL, it displays 1.
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 */ Looking into the MySQL manual, they seem to be synonyms really.
MySQL manual on IS NULL
MySQL manual on ISNULL()
and even if they aren't, I would tend to trust the query optimizer to pick the best solution.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
