Wednesday, June 8, 2016

SQL - COALESCE() vs ISNULL()

In SQL Server, we can use the ISNULL function or COALESCE function to check the null value for a column or variable. 
Both functions are doing the same thing but they have some characteristics which are disagree with each other. In this section, we are going to differentiate both functions as –
ISNULL is known as SQL-ANSI non-standard function which is not feasible across database platforms and it is Sybase/SQL Server specific only.
COALESCE is known as SQL-ANSI standard function which is portable across database platforms and cross limits to use freely.
It’s a bit more limited in the sense that it supports only two inputs. ISNULL seems easier to read since it seems clearer what it’s doing inside because ISNULL is the same as the first argument.
It’s more advanced in the sense that it supports only n-number inputs and returns the first Not null expression among its arguments or we can say that COALESCE is the highest according to datatype precedence.
ISNULL is an internal engine function and usually used because it's more predictable.
COALESCE is internally based on the CASE expressions and COALESCE is harder to spell, but doesn't lead to incorrect assumptions.
Due to SQL-ANSI non-standard nature, it’s more flexibility. Performance wise is faster but more limited due to having only 2 arguments.
COALESCE has a lot of flexibility due to having n-number of arguments but performance wise is slower than ISNULL.
ISNULL has to deal with a fixed number of input variables (2).
COALESCE has to be designed to work with any number of variables, so this will invariably involve extra computations.
Syntax
ISNULL(check_expression, replacement_value)
Returns the same type as check_expression.

SELECT
ISNULL(CAST(NULL AS INT), 235.5)
-- Returns 235

SELECT
ISNULL(CAST(NULL AS VARCHAR(5)), 'Hello Ryan')
-- Returns value within specified length as Ryan

Syntax
COALESCE( expression [ ,...n ] )
Returns the data type of the expression with the highest data type precedence.

SELECT COALESCE(CAST(NULL AS INT), 235.5)
-- Returns 235.5
SELECT COALESCE(CAST(NULL AS VARCHAR(5)), 'Hello Ryan')
-- Returns all value as Hello Ryan


Conclusion
Generally, it is recommended to stick to standard features unless there’s some flexibility or performance advantage in the non-standard feature that is a higher priority. ISNULL is actually more limited than COALESCE, so generally, it is recommended to stick to COALESCE.

2 comments:

  1. Hello Mukesh, thanks for your comment on my article Difference between ISNULL and COALESCE in SQL SERVER. I see you have also shared very good information.

    Keep it up.

    ReplyDelete
  2. Very informative article. Once typo in illustration:
    SELECT
    ISNULL(CAST(NULL AS VARCHAR(5)), 'Hello Ryan') return "Hello" not "Ryan" as mentioned above. Thanks for that information.

    ReplyDelete

Popular Posts

Get Sponsored by Big Brands