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 a 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 a good example of ISNULL from BOL:
USE AdventureWorks; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO
Observation:
An interesting observation is NULLIF returns null if it comparison is successful, whereas it 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?
Sidenote: In the SQL Server world, I have seen lots of people getting confused with NULL. They think null is nothing, or null is undefined or is null infinite. Actually, none of the answer is true. Null has been just Null. You can’t compare the null with any other value. This is the reason when we compare null with any other value we get the answer as null. Additionally, when we compare null to null, we get false results because there is no way to identify NULL with any value.
Reference: Pinal Dave (https://blog.sqlauthority.com)
30 Comments. Leave new
Thanks, Nice tip
Maybe this ?
IsNull(null, nullif(1, 1)), but I guess it would just return null :P
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’
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’?
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.
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.
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
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.
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.
good one
They should be called NULLIF and IFNULL to show they are truly opposites.
hi..
Thxs for the help.. which i got from ur website..
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?
select 1/0
its shows error ….
plz help me???
select 1/0
its shows error ….
plz help me???
It will show you divide by 0 error which is expectable. What do you want to return instead of error?
Thanks alot sir
Hi Sir, what is dynamic query? why we need dynamic query?
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!
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)
Thanks, your simple and straightforward blog has helped me solve many quick references issues/questions. Keep up the good work :)
Pinal, your entire blog is fantastic. You deserve every bit of praise you have ever received for your work. Thank you.