A common requirement in SQL is to identify and return non NULL values in SQL from a list of possible values.
ISNULL function can be used when its a simple case of returning a value in place of a NULL, for example in the below code the first value is evaluated and if it is a NULL then the second value is returned,
SELECT ISNULL(NULL,'ABC') -- RETURNS: ABC
ISNULL function is great if there is only one value to evaluate, if there is more than one value then we could embed
ISNULL functions to get the desired effect but this would be a little messy, an example of this is below,
SELECT ISNULL(NULL,ISNULL(NULL,'ABC')) -- RETURNS: ABC
Thankfully SQL has the
COALESCE function which will return the first non NULL value it finds and it excepts a parameter array as an input, for example in the below example the
COALESCE function will evaluate all the parameters from left to right and return the first non NULL value it gets to,
SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,'ABC',NULL) -- RETURNS: ABC
Just to emphasise if two non NULL values are defined then its the first value from left to right that will be returned, so in the below example the value 5 is returned.
SELECT COALESCE(NULL,5,NULL,NULL,NULL,'ABC',NULL) -- RETURNS: 5
For more information on the
COALESCE function you can check out the following MSDN article http://msdn.microsoft.com/en-us/library/ms190349.aspx