What to use ISNULL() or COALESCE()

Introduction

One of my Blog post is very much appritiated by my reader is different between ISNULL() and COALESCE (). But in early time I post my blogs at http://sqlservernet.blogspot.in/2012/03/what-to-use-isnull-or-coalesce.html

So I decide to move this post in my web that all my reader can enjoy it.

Let's start

The NULL value always makes some problem to database developer. To handle the null value properly the SQL server provides two functions:

1.    ISNULL()

2.    COALESCE ()

Both functions act like same.

Before finding the difference between two functions we must understand, what is the functionality of both functions to take care of null values.

Suppose we have a Table names TBL-A

Roll	StudentName	StudentClass
1	JOYDEEP	1
2	TUHIN	1
3	NULL	1

To handle the NULL value with ISNULL()

SELECT ISNULL(StudentName,'NOT FOUND') StudentName

FROM   Table1-A

It returns "NOT FOUND" in studentname columns for roll = 3

The syntax of the COALESCE is mentioned bellow

COALESCE ( expression  [ ,...n ] )

Unlike ISNULL, COALESCE takes multiple columns as expression. To understand it properly I give an example. Please study the example well and you will understand the COALESCE

CREATE TABLE dbo.wages

(

    emp_id             tinyint   identity,

    hourly_wage   decimal   NULL,

    salary               decimal   NULL,

    commission     decimal   NULL,

    num_sales       tinyint      NULL

);

GO

INSERT dbo.wages

    (hourly_wage, salary, commission, num_sales)

VALUES

    (10.00, NULL, NULL, NULL),

    (20.00, NULL, NULL, NULL),

    (30.00, NULL, NULL, NULL),

    (40.00, NULL, NULL, NULL),

    (NULL, 10000.00, NULL, NULL),

    (NULL, 20000.00, NULL, NULL),

    (NULL, 30000.00, NULL, NULL),

    (NULL, 40000.00, NULL, NULL),

    (NULL, NULL, 15000, 3),

    (NULL, NULL, 25000, 2),

    (NULL, NULL, 20000, 6),

    (NULL, NULL, 14000, 4);

GO

SELECT 

CAST(COALESCE(hourly_wage * 40 * 52,  salary,

                                commission * num_sales) AS money) AS 'Total Salary'

FROM dbo.wages

ORDER BY 'Total Salary';

The Output is mentioned bellow

 Total Salary

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

Now what are the differences between them?

1.    COALESCE is ANSI-92 compliant and ISNULL is not. So if u migrate the SQL statement in any          other RDBMS the COALESCE is helpful.

2.    COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order          to compare three expressions with ISNULL, you would have to nest expressions:

       SELECT ISNULL(ISNULL(Col1, Col2), Col3)

3.    In this SQL the ISNULL can provide some Error

       DECLARE @Field1 char(3),

               @Field2 char(50)

      SET @Field2 = 'Some Long String'

      SELECT ISNULL(@Field1, @Field2)

      --Returns 'Som'

      SELECT COALESCE(@Field1, @Field2)

      --Returns 'Some Long String'

 4.    Performance wise ISNULL and COALESCE are equal. It is found that the ISNULL is  

        slightly better performer then COALESCE.

In my advice use COALESCES() rather than ISNULL().

Hope you like this.

Posted by: MR. JOYDEEP DAS