Tuesday 8 January 2013

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.

Posted by: MR. JOYDEEP DAS

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I like to use NULLIF when validating SP parameters:

    DECLARE @someVariable VARCHAR(10)

    -- Unit testing data
    --SELECT @someVariable = 'X' -- valid value assigned
    --SELECT @someVariable = '' -- blank value assigned
    --SELECT @someVariable = NULL -- NULL assigned


    -- If NULL or Blank - Display Variable
    IF NULLIF(@someVariable, '') IS NULL
    BEGIN
    SELECT @someVariable
    END

    ReplyDelete