SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Following is good example of NULLIF and CASE from BOL:
USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM
Production.Product
WHERE ProductID < 10;
GO

Explanation of ISNULL
Syntax:
ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

Following is good example of ISNULL from BOL:
USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Observation:
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.

Puzzle:
How to create infinite loop using NULLIF and ISNULL? If this is even possible?

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

24 thoughts on “SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

  1. SET ANSI_NULLS OFF

    DECLARE @test AS INT
    SET @test = 1

    WHILE ISNULL( NULLIF(@test, @test), @test ) = @test
    BEGIN
    PRINT ‘HI’
    BREAK
    END

    Making @test = NULL would still make it print ‘HI’

  2. Hello The Waterproof SQL Champ good work,

    I was expecting it to be done without using WHILE but I did not make that clear in question so it works.
    Good Work. I modified your code to show how the infinite code can be generated. However following script will run till 10 iteration and will stop.

    SET ANSI_NULLS OFF

    DECLARE @test AS INT
    SET @test = 1

    WHILE ISNULL( NULLIF(@test, @test), @test ) = @test
    BEGIN
    PRINT ‘HI’
    SET @test = @test + 1
    IF @test > 10
    BREAK
    END
    GO

    Warning : Do not run this code on production machine. Make sure you test it. The reason it is published here, so user who have similar problem can prevent infinite loop from happening.

  3. I have the following code that uses the IsNull function.

    declare @Test bit;
    set @Test = ‘false’;

    select ( Case When IsNull(@Test, ”) = ”
    Then ‘IS NULL’
    Else ‘NOT NULL’
    End);

    I would expect ‘NOT NULL’ to be returned as @Test is set to a value ‘false’. However ‘IS NULL’ is output instead.

    Does SQL Server treat NULL the same as ‘false’?

  4. Yasar,
    You should rephrase this to something like this

    declare @Test bit
    set @Test = NULL
    /*
    Change to
    set @Test = 0 or
    set @Test = 1
    keeping every other SET line remarked to check different
    behaviours.
    */

    select Case IsNull(@Test, 0) —
    when 0 Then ‘IS NULL’
    else ‘NOT NULL’
    End Result

    That will work just fine.

  5. declare @Test tinyint
    set @Test = NULL
    /*
    Change to
    set @Test = 0 or
    set @Test = 1
    keeping every other SET line remarked to check different
    behaviours.
    */

    select Case IsNull(@Test, 2) —
    when 2 Then ‘IS NULL’
    else ‘NOT NULL’
    End Result

    That should do it.

  6. I have a serious of bit values. I want to display column if bit value is 1 and hide column if bit value is 0. How can I achieve this. Here is what I have so far.

    ALTER PROCEDURE [dbo].[sp_datadump_selectfromcheckbox]
    @project_ID int,
    @Title_isChecked bit

    AS
    BEGIN
    SELECT PRSDATA.Anomaly_ID,

    CASE WHEN @Title_isChecked =’1′ THEN (Select PRSDATA.Title) END ‘ ‘

    from PRSDATA,PRSDATA1, PRSDATA2, PRSDATA3, PRSDATA4, PRSDATA5, PRSDATA6, PRSDATA7 , PRSDATA8
    WHERE PRSDATA.anomaly_id = PRSDATA1.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA2.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA3.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA4.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA5.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA6.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA7.anomaly_id and
    PRSDATA.anomaly_id = PRSDATA8.anomaly_id and
    PRSDATA.Main_Project_Affected = @project_ID

    END
    if (@@error = 0)
    begin
    RETURN 0
    end
    else
    begin
    RETURN 1
    end

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  8. Now, here’s a fun one… if you don’t want to set ansi_nulls, you can use the isnull()/nullif() combo when comparing two values in a table.

    The trick is in this line: isnull(nullif(val1, val2), nullif(val2, val1)) is null

    ————————————–
    ————————————–
    if object_id(‘tempdb..#test’) is not null drop table #test
    create table #test (
    val1 varchar(50),
    val2 varchar(50)
    )

    insert into #test
    select ‘Bill’, ‘Bill’
    union all
    select null, null
    union all
    select ‘Bill’, null
    union all
    select ‘Bill’, ‘Gates’
    union all
    select null, ‘Gates’

    select
    val1,
    val2,
    case
    when val1 = val2 then ‘(wrong) equal’
    else ”
    end as WrongEquals,
    case
    when (val1 val2) then ‘(wrong) NOT equal’
    else ”
    end as WrongNotEquals,
    NULLIF(val1, val2) as NullIf1_2,
    NULLIF(val2, val1) as NullIf2_1,
    case
    when isnull(nullif(val1, val2), nullif(val2, val1)) is null then ‘eqivalent!’
    else ”
    end as OddlyCorrect_Equivalent,
    case
    when isnull(nullif(val1, val2), nullif(val2, val1)) is not null then ‘NOT eqivalent!’
    else ”
    end as OddlyCorrect_NotEquivalent
    from
    #test
    ————————————–
    ————————————–

    Enjoy wrapping your mind around that.

  9. We got huge performance issue whenever we use NULLIF instead of IsNull() in Where clause

    Example:

    NullIf(column,”) is not null

    So we replaced

    isNull(column,”) ”

    I don’t understand the problem.

  10. hello i am uday satardekar from ghotgewadi ,dodamarg.
    and i select email from table but if depends on 4 combo box.if one null then omitt it.how?

  11. Hi Pinal,

    I have a problem with the ISNULL function. In our table, for a particular column-col1, the values look like

    1.
    2.
    3. NULL
    The row 2 value looks like an empty string as I tried copy pasting it to notepad and it showed nothing.

    The below queries however do not return rows with these values though
    Select * from where LTRIM(RTRIM(Col1))=”
    Select * from where ISNULL(LTRIM(RTRIM(col1)),”)=”
    Select * from where Col1 IS NULL
    Select * from where Col1 IS NULL

    However I get these rows in my result set if I re-write the query as :
    Select * from where ASCII(Col1)=0

    Also, SELECT Len(Col1) from returns 40 for these rows.

    Little confused as to why this happens!

  12. A small update on this question. The below query works.
    DECLARE @SQL VARCHAR(1000)
    SET @SQL=’ SELECT * FROM tbl
    WHERE LTRIM(RTRIM(col)) LIKE ”%’+CHAR(’0′) +’%”

    EXEC(@SQL)

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s