ISNULL vs. COALESE in T-SQL
As many SQL developers know, COALESCE and ISNULL can be used to achieve the same results. They both essentially check if a value is NULL and return something else if it is. Both functions are widely used from earlier versions of SQL to SQL Sever 2008 development. For anyone needing to brush up on their syntax, here are their MSDN definitions.
COALESCE
ISNULL
Some of the main advantages of using COALESCE over ISNULL are
Multiple inputs are accepted - all of which are checked for NULL. The first parameter that equates to a non-null value will be returned. ISNULL only checks one parameter for NULL and strictly returns the first parameter's value if it's a non-null value, and returns the second parameter's value if the first value is NULL.NULL CAN be returned. If all values in a COALESCE statement are NULL - NULL is what will be returned. ISNULL doesn't allow for this flexibility, as a non-null value must be returned.
On the flip-side, some of the advantages of using ISNULL are
It's more efficient. A SQL statement including an ISNULL statement will always execute at least as fast, if not faster than a COALESCE statement.Readability. Any programmer could probably figure out what an ISNULL statement is doing. COALESCE may take the average developer a google search or two to figure out.Filed Under DatabaseTags:
coalesce,
efficiency,
isnull,
mysql,
null,
sql functions,
t-sqlComments
