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