Menu
These two MySQL functions do the same thing:
IFNULL(column_name, 'test') = 'test' or
NULLIF(column_name, 'test') IS NULL Which one is more efficient?
408
IFNULL is equivalent to ISNULL. IFNULL is equivalent to COALESCE except that IFNULL is called with only two arguments. ISNULL(a,b) is different from x IS NULL . The arguments can have any data type supported by Vertica.
The IFNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression.
We often use the NULLIF function when the database contains “special” values such as zero or empty string that we want to handle them as NULL values. This is very useful when we use the aggregate functions such as AVG, MAX, MIN, SUM, and COUNT.
The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.
They are both as efficient as each other - the functions have about the same overhead as each other.
But this is more efficient than either:
(column_name is null or column_name = 'test') Because the function doesn't need to be invoked.
You may find putting the more commonly encountered test first improves performance.
With questions like this, the simplest and more reliable way to discover relative performance is to just try them and compare query timings. Make sure you have production-sized and realistic-valued datasets so the test is fair.
96
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
