Thursday, August 8, 2013

Understanding NULLIF and ISNULL

Understanding NULLIF and ISNULL

Introduction
One of my friends wants to know about two functions NULLIF and ISNULL. In this article I am trying to discuss about that.
Understanding NULLIF
First we look at the syntax of the NULLIF
The syntax is:
NULLIF(Expression, Expression)
Return type:
Return type is the same type of the first expression or NULL
How it works:
NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL. NULLIF returns the first expression if the two expressions are not equal.
Example of NULLIF
Step-1 [ Create the Base Table ]
IF OBJECT_ID('Tbl_DEVGROUP') IS NOT NULL
   BEGIN
     DROP TABLE Tbl_DEVGROUP;
   END
GO  
CREATE TABLE Tbl_DEVGROUP
       (DEVID    INT          NOT NULL IDENTITY PRIMARY KEY,
        DEVNAME  VARCHAR(50)  NOT NULL,
        LANG     VARCHAR(50)  NOT NULL);
GO
INSERT INTO  Tbl_DEVGROUP
       (DEVNAME, LANG)
VALUES ('SANGRAM', '.NET'),
       ('TUHIN', 'C++'),
       ('ROOHUL', 'VC++'),
       ('SUKAMAL', 'SQL')
GO       
    
Step-2 [ Using NULLIF and Compare the Output ]
-- Returns NULL Value if Expression1 and Expression2 are same
SELECT DEVID, DEVNAME, NULLIF(LANG, '.NET') AS 'Null if Equal'
FROM   Tbl_DEVGROUP
WHERE  DEVID = 1;
GO

-- Output

DEVID DEVNAME    Null if Equal
1     SANGRAM    NULL   

GO

-- Returns Expression1 Value if Expression1 and Expression2 are not same
SELECT DEVID, DEVNAME, NULLIF(LANG, 'C++') AS 'Null if Equal'
FROM   Tbl_DEVGROUP
WHERE  DEVID = 1;
GO

-- Output
DEVID   DEVNAME   Null if Equal
1       SANGRAM   .NET

Understanding ISNULL
First of all we look at the syntax.
The syntax is:
ISNULL ( check_expression , replacement_value )
Arguments:
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Return Type:
Return the same type of check expression.
How it works:
If the Check expression is not null then it returns the check expression. If the check expression is NULL it returns the replacement value.
Example of ISNULL
Step-1 [ Create the Base Table ]
IF OBJECT_ID('Tbl_DEVGROUP') IS NOT NULL
   BEGIN
     DROP TABLE Tbl_DEVGROUP;
   END
GO  
CREATE TABLE Tbl_DEVGROUP
       (DEVID    INT          NOT NULL IDENTITY PRIMARY KEY,
        DEVNAME  VARCHAR(50)  NOT NULL,
        LANG     VARCHAR(50)  NULL);
GO
INSERT INTO  Tbl_DEVGROUP
       (DEVNAME, LANG)
VALUES ('SANGRAM', '.NET'),
       ('TUHIN', NULL),
       ('ROOHUL', 'VC++'),
       ('SUKAMAL', NULL)
GO       

Step-2 [ Using ISNULL ]
-- Checking the Null Value only
SELECT DEVID, DEVNAME, LANG
FROM   Tbl_DEVGROUP
WHERE  LANG IS NULL;

-- Alternate way
SELECT DEVID, DEVNAME, LANG
FROM   Tbl_DEVGROUP
WHERE  ISNULL(LANG, '') = '';
GO

-- Output
DEVID       DEVNAME     LANG
2           TUHIN       NULL
4           SUKAMAL     NULL  


-- Null Replacement with ISNULL
SELECT DEVID, DEVNAME, ISNULL(LANG, 'NOT DEFINE') AS LANG
FROM   Tbl_DEVGROUP;
GO

-- Output
DEVID       DEVNAME           LANG
1           SANGRAM           .NET
2           TUHIN             NOT DEFINE
3           ROOHUL            VC++
4           SUKAMAL           NOT DEFINE

Hope you like it.



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 athttp://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().