Home NULLIF vs IFNULL
Reply: 1

NULLIF vs IFNULL

pfandflaschen
1#
pfandflaschen Published in 2017-12-07 22:23:38Z

Could you explain to me what is the difference between the sql-functions "NULLIF" and "IFNULL"?

Used_By_Already
2#
Used_By_Already Reply to 2017-12-07 22:44:39Z
IFNULL(value-to-test, action-if-tested-as-null )

#equivalent case expression:
CASE WHEN value-to-test IS NULL THEN action-if-tested-as-null ELSE value-to-test END

IFNULL directs the query to output the second parameter if the first paramenetr happens to be NULL, otherwise it outputs the first parameter. IFNULL is a MySQL only function name, the SQL standard alternative is COALESCE

NULLIF(value-to-compare-a, value-to-compare-b )

#equivalent case expression:
CASE WHEN value-to-compare-a = value-to-compare-b THEN null ELSE value-to-compare-a END

NULLIF compares 2 values. If BOTH values are the SAME then it returns NULL otherwise it returns the first parameter.

Personally I always prefer to use COALESCE instead of IFNULL.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.333426 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO