Isnull function

Question

Post reply

Isnull function

karthik M

One Orange Chip

Points: 29139
More actions
December 21, 2007 at 12:58 am

#63133

Jeff,
WHERE ISNULL(SomeColumn, ' ') <> ' '
Actually there is nothing wrong with this statement.But it is not required.
The logic of this sql code is :
if 'Somecolumn' has 'NULL' value,it was replaced by ' '(blank space).
But as per the condition it should not equal to ' '(blank space).
So,here,changing NULL values to '' is not required.
Instead of doing like above,We can use the below one.
where somecolumn is not null
1) Changing Null values to '' time will be saved.
2) we can avoid <> operator which cause or leads to Table Scan method.
Am i correct ? kindly add your valuable suggestions.
karthik

Viewing 15 posts - 1 through 15 (of 57 total)

You must be logged in to reply to this topic. Login to reply

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 1

They don't mean the same thing.

Try

DECLARE @T TABLE (A int, B char(1))

INSERT INTO @T SELECT 1, 'A' UNION SELECT 2, '' UNION SELECT 3, NULL

SELECT * FROM @T WHERE B IS NOT NULL

SELECT * FROM @T WHERE IsNull(B, '') <> ''

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

karthikeyan (12/21/2007)
Jeff,
WHERE ISNULL(SomeColumn, ' ') <> ' '
Actually there is nothing wrong with this statement.But it is not required.
The logic of this sql code is :
if 'Somecolumn' has 'NULL' value,it was replaced by ' '(blank space).
But as per the condition it should not equal to ' '(blank space).
So,here,changing NULL values to '' is not required.
Instead of doing like above,We can use the below one.
where somecolumn is not null
1) Changing Null values to '' time will be saved.
2) we can avoid <> operator which cause or leads to Table Scan method.
Am i correct ? kindly add your valuable suggestions.

WHERE somecolumn is not null will not exclude blanks like the original code does. The original code excludes both NULLs and Blanks. You are correct that we should probably get away from using <> but we should also get away from using a formula on a column name because it won't allow Index Seeks.

Hint: The orginal code is a very common mistake that developers make because they don't understand how NULLs work.

Try again... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Hint

DECLARE @Var1 INT

SET @Var1 = NULL

SELECT

CASE WHEN @Var1 = NULL THEN 'Var1 = Null' ELSE 'Var1 <> NULL' END AS EqualityTest,

CASE WHEN @Var1 <> NULL THEN 'Var1 <> Null' ELSE 'Var1 = NULL' END AS InequalityTest

Look at the results and figure out why they are as they are. You should be able to figure it out. Check Books Online for more info on comparisons with null.

Hmm. I think I'm going to blog on this.... later

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Now, thats a "hint" if I ever saw one... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Too easy?

Ah well, we are here to help people out after all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

Apparently not... I've still not seen the correct answer from Karthik.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

It is a Saturday. Normal people don't frequent work-related boards over a weekend

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 8

Heh... says a lot about whether thee and me are normal, huh?

Merry Christmas, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

And to you and your family

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 10

GilaMonster (12/21/2007)
Hint
DECLARE @Var1 INT
SET @Var1 = NULL
SELECT
CASE WHEN @Var1 = NULL THEN 'Var1 = Null' ELSE 'Var1 <> NULL' END AS EqualityTest,
CASE WHEN @Var1 <> NULL THEN 'Var1 <> Null' ELSE 'Var1 = NULL' END AS InequalityTest
Look at the results and figure out why they are as they are. You should be able to figure it out. Check Books Online for more info on comparisons with null.
Hmm. I think I'm going to blog on this.... later

I dont understand what you meant

You need to use IS NULL or IS NOT NULL to check againt NULL

DECLARE @Var1 INT

SET @Var1 = NULL

select case when @var1=null then 'it is null' else 'not null' end

select case when @var1 is null then 'it is null' else 'not null' end

See what you get

Madhivanan

Failing to plan is Planning to fail

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Madhivanan (12/28/2007)
You need to use IS NULL or IS NOT NULL to check againt NULL

Yes. My point exactly. Do you know why?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 12

Madhivanan (12/28/2007)
You need to use IS NULL or IS NOT NULL to check againt NULL

Actually, for the problem I posted, that's absolutely NOT true. Do you know why? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 13

Ok... enough suspense... I'll tell you why... first a review of the dreadful mistake that a lot of developers make. A developer has to solve the problem of returning all the columns of a table where, say, Column2 is NOT NULL and NOT BLANK. Now, forget for a minute that I'm using SELECT *, eh? The thing we're talking about is the "Death by SQL" contained in the WHERE clause. Most developers would solve the NOT NULL NOT BLANK in Column2 using the following code...

SELECT *

FROM sometable

WHERE ISNULL(Column2, ' ') <> ' '

A much wiser but still incorrect developer would say, "No, no... the function on Column2 will prevent Index Seeks... write it this way..."

SELECT *

FROM sometable

WHERE Column2 IS NOT NULL

AND Column2 <> ' '

Yep... you heard me... that's still incorrect! Why? (insert Jeapordy theme song here)

.

BECAUSE IF YOU JUST CHECK FOR BLANKS, THE NULLS WILL BE IGNORED BECAUSE YOU CAN'T USE A RELATIONAL OPERATOR TO CHECK FOR NULLS ANYWAY!

So, the proper way to write the code to make sure that Column2 is NOT NULL and NOT BLANK is very simple...

SELECT *

FROM sometable

WHERE Column2 > ' '

...and we even avoided the "<>" relationship, to boot. 😛

Now... anyone want to figure out why the WHERE clauses in the following snippets of code are both wrong and then explain why? 😉

SELECT *

FROM sometable

WHERE ISNULL(Column2, 0) <> 0

SELECT *

FROM sometable

WHERE Column2 IS NOT NULL

AND Column2 <> 0

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 14

A Little late here,As per SQL-92 standard,if ANSI_NULLS option is on, will return FALSE only.

So before executing this code

------------------------------

DECLARE @Var1 INT

SET @Var1 = NULL

SELECT

CASE WHEN @Var1 = NULL THEN 'Var1 = Null' ELSE 'Var1 <> NULL' END AS EqualityTest,

CASE WHEN @Var1 <> NULL THEN 'Var1 <> Null' ELSE 'Var1 = NULL' END AS InequalityTest

--------------------------------

we need to execute SET ANSI_NULLS OFF.Otherwise the above statement will give wrong output.

SET ANSI_NULLS ON

DECLARE @Var1 INT

SET @Var1 = NULL

SELECT

CASE WHEN @Var1 = NULL THEN 'Var1 = Null' ELSE 'Var1 <> NULL' END AS EqualityTest,

CASE WHEN @Var1 <> NULL THEN 'Var1 <> Null' ELSE 'Var1 = NULL' END AS InequalityTest

Output:

Var1<>Null

Var1=Null

In both the cases conditon fail because of SQL-92 standard rule.

SET ANSI_NULLS OFF

DECLARE @Var1 INT

SET @Var1 = NULL

SELECT

CASE WHEN @Var1 = NULL THEN 'Var1 = Null' ELSE 'Var1 <> NULL' END AS EqualityTest,

CASE WHEN @Var1 <> NULL THEN 'Var1 <> Null' ELSE 'Var1 = NULL' END AS InequalityTest

Output:

Var1 =Null

I want to add couple of points.

1) 'No Two NULLS are Equal'.

2) In Sql2000,default value for ANSI_NULLS is ON.

Also,

1) Use is null in the where clause ( ex: where somecolumn is null )instead of = null, when ANSI_NULLS option is turn to ON.

2)if we use isnull it should eliminate the rows which contain Blank Space.

Jeff,

Am i correct?.

karthik