Isnull function

Question

Isnull function

Viewing 15 posts - 16 through 30 (of 57 total)

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

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

Jeff,

SELECT * FROM sometable WHERE ISNULL(Column2, 0) <> 0
SELECT * FROM sometable WHERE Column2 IS NOT NULL AND Column2 <> 0

As you explained in your previous examples,

SELECT * FROM sometable

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

It Leads to TableScan.

SELECT * FROM sometable

WHERE Column2 IS NOT NULL

AND Column2 <> 0

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 Better way is,

Select * from sometable

where Column2 > 0

Will eliminate NOTNULL and ZERO.Am i correct ?

karthik

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

You missed the point.

NULL is not a value. It's a placeholder for 'unknown'. Because it is an unknown value, it is not equal to anything. It's not unequal to anything either.

The expression (@Var = NULL) does not return true or false. It returns null. That's why you get the 'strange' behaviour of the case statement that I posted. Not because you can't compare nulls with a =. You can compare variables to null with any of the usual operators (=, !=, >, <). The comparison will just return NULL. Always.

This is what is sometimes refered to as 3-state logic, and it's something a lot of people struggle with.

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

Yes, but the point that Jeff is trying to make is that the IsNull is not necessary, sicne a comparison with ' ' will also eliminate nulls, since nulls do not return true when compared to anything.

So ISNULL(Column, ' ') != ' ' is equivalent in results to

Column != ' '

Since if the column is null, the comparison with ' ' will result in NULL. Only rowswhere the condition evaluates to TRUE will go through

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

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

karthikeyan (12/31/2007)
SELECT * FROM sometable
WHERE Column2 IS NOT NULL
AND Column2 <> 0
So the Better way is,
Select * from sometable
where Column2 > 0
Will eliminate NOTNULL and ZERO.Am i correct ?

Close, but the two queries are not equivalent. What about a case where Column2 = -1. The first query will return that, yours won't.

You're correct on the null. Jeff's query won't return rows where column2 is null or zero. Yours won't return results where column2 is null or less than or equal to zero.

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

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

Yes I got it.But,

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
Var1 =Null

I have executed the above statement with SET ANSI_NULLS ON & SET ANSI_NULLS OFF in my home PC.it gave me the mentioned output.

karthik

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

I did see your post. And, no, you don't "need to execute SET ANSI_NULLS OFF.Otherwise the above statement will give wrong output."

I specifically wrote that to illustrate how nulls behave when ansi_nulls is on. (the default and recommended setting) The example returned exactly what I intended it to return.

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

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

As you told if we want to display -1 or some other negative values,

we need to write,

select * from sometable where column > 0

select * from sometable where column < 0

Am i correct ?

select * from sometable where column > ' '

I have some questions.

1) l eliminate NULL ans BlankSpace only or something else ?

2) we should not use = Null (or) IS NULL in the where clause.

3) we should not use Null with any relational operator.

4) we should avoid is not null also in the where clause.

karthik

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

Again my #1 question,

will column > ' ' eliminate NULL and BlankSpace only or something else ?

karthik

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

karthikeyan (12/31/2007)
As you told if we want to display -1 or some other negative values,
we need to write,
select * from sometable where column > 0
select * from sometable where column < 0
Am i correct ?

Why two queries?

select * from sometable where column <> 0

Will get you all rows where column is not NULL and is not 0. Which is what you need.

select * from sometable where column > ' '
I have some questions.
1) l eliminate NULL ans BlankSpace only or something else ?

Only Null and '', since for a string comparison, there's nothing less than an empty string.

2) we should not use = Null (or) IS NULL in the where clause.

You shouldn't use = NULL as it's never true.

As for IS NULL, that depends what you're trying to do. If you want a query to find all the rows where a certain column is null, then you use IS NULL.

3) we should not use Null with any relational operator.

Correct, as it will evaluate to NULL, not true or false.

4) we should avoid is not null also in the where clause.

Again, it depends what you need and what you're trying to do. If you want to find all the rows in the table where a certain column is not null, and you have no other criteia, then you use IS NOT NULL. That's what it's there for.

You just need to check and ensure that you don't have redundant conditions

(like this example: WHERE Column > 0 and Column = 5 AND column IS NOT NULL)

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

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

Why two queries?
select * from sometable where column <> 0

Yes i do agree.I think your query(column 0) will use TableScan instead of Index.

karthik

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

karthik M

One Orange Chip

Points: 29139

December 31, 2007 at 2:55 am

#763632

<> Operator perform TableScan method.

karthik

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

karthikeyan (12/31/2007)
Yes i do agree.I think your query(column 0) will use TableScan instead of Index.

Possibly, but if the requirement is that all rows other than where column=0 or IS NULL are returned in a single result set, then you use <>.

Whether it's in one query or two, the same amount of data is returned and the same work has to be done.

Besides, it's quite likely that, depending on the number of rows affected, the columns needed and necessary lookups to the cluster/heap, one or both of the inequalities will table/index scan.

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: 1004290 More actions · Answer 12

karthikeyan (12/31/2007)
<> Operator perform TableScan method.

Or, if the indexes are correct... an Index Scan which can be a little faster because there's usually fewer pages to step through. If you have a full covering index, it can give you an Index Seek, but don't be fooled... it's really an Index Scan because it has to look through most of the index.

Splitting <> into two separate criteria does nothing for performance. Optimizer treats both the same way so far as CPU Time and execution plans goes... During an Index Seek, optimizer splits them with an OR, so save yourself some typing and use <>.

--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 13

Thanks Gila and Jeff.

From our discussion,The following points should be kept in mind when we use NULL.

1. We should avoid comparison of NULL with relational operator.

i.e = NULL (or) <> NULL etc.,

2. SQL2000's default option is SET ANSI_NULLS ON. We should not modify it to OFF.

3. Use 'IS NULL' (or) 'IS NOT NULL',when necessary.

4. Where isnull(Somecomun,'') <> '' has to be written in the below format.

Where somecolumn > '' ( If we faced the above situation in future)

5.Where isnull(somecolumn,0) <> 0 has to be written in the below format.

where somecolumn <> 0 ( If we faced the above situation in future)

6. No Two NULL's are equal.

7. NULL + anything = NULL.i.e Select NULL+5 Answer : NULL

8. NULL mean 'UnKnown' value.So when we compare NULL with any value it return 'UNKNOWN' value only.

9. We should avoid NULL columns while designing the table.If necessary,we can create NULL valued columns otherwise best thing is to create Columns with NOTNULL(Default).

10.When we set SET ANSI_NULLS OFF,We can compare NULL with relational operator.But it should give wrong output also leads to some confusion.

Am i correct ?

karthik

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 14

Pretty much spot on...

--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)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 15

Ok probably one of the more interesting discussions on NULL and comparison operators but I have a question. I feel like I missed the 1st half of the discussion. Where is the original question/statement that Jeff posted?

Also just to be sure I understand correctly. A <> is ALWAYS going to do a table scan? And from what you said earlier Jeff

SELECT * FROM sometable WHERE somecolumn > 0 OR somecolumn < 0

will also do a table scan and can't be optimized?

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]