null problem in select query

Question

Post reply

null problem in select query

rexyrexin

Mr or Mrs. 500

Points: 562
More actions
March 18, 2015 at 3:57 am

#316328

Suppose I have added a xyz bit column in mytable. From now onwards new values inserted in mytable will have 0 or 1 in xyz column but the values that were previously stored will have NULL value in xyz column.
Now I want to write a query in which I will not give xyz parameter always like it can be null or value.
select * from mytable
where class='something'
and xyz is null
or
select * from mytable
where class='something'
and xyz = 1
Now how to write same query for both cases.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

select * from mytable

where class='something'

and (xyz is null OR xyz = 1);

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rexyrexin Mr or Mrs. 500 Points: 562 More actions · Answer 2

select * from mytable

where class='something'

and (xyz is null OR xyz = 1);

But xyz can either be NULL or 1. I mean

select * from mytable

where class='something'

and xyz IS NULL

and

select * from mytable

where class='something'

and xyz = 1

DaPainKiller SSChasing Mays Points: 641 More actions · Answer 3

select * from mytable

where class='something'

and ISNULL(xyz, 99) = 1);

Emil B SSCertifiable Points: 5659 More actions · Answer 4

rexyrexin (3/18/2015)
select * from mytable
where class='something'
and (xyz is null OR xyz = 1);
But xyz can either be NULL or 1. I mean

Thats why you have OR in between to cover both cases (OR im not getting it :hehe:)

rexyrexin Mr or Mrs. 500 Points: 562 More actions · Answer 5

select * from mytable

where class='something'

and ISNULL(xyz, 99) = 1);

What is 99?

Thats why you have OR in between to cover both cases (OR im not getting it [Hehe] )

But that I will bring result of both cases. I want only records when xyz = 1 at a time.

Emil B SSCertifiable Points: 5659 More actions · Answer 6

Is your "1" a parameter/variable that you are providing?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

rexyrexin (3/18/2015)
select * from mytable
where class='something'
and ISNULL(xyz, 99) = 1);
What is 99?

A dummy value. Something that is definately not equal to 1.

rexyrexin (3/18/2015)
Thats why you have OR in between to cover both cases (OR im not getting it [Hehe] )
But that I will bring result of both cases. I want only records when xyz = 1 at a time.

Then what's wrong with this?

select * from mytable

where class='something'

and xyz = 1

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Emil B SSCertifiable Points: 5659 More actions · Answer 8

ok If your "1" value is in fact a parameter and you want your results to be based on that parameter having a null value or 1 or 0 you can use a dummy value like already mentioned and write something like below:

@Param

select *

from mytable

where class='something'

and ISNULL(xyz,-1) = ISNULL(@Param, -1)

-1 is a dummy value to be able to compare NULL's, taking into account xyz column won't have different values than 0 or 1 or NULL

rexyrexin Mr or Mrs. 500 Points: 562 More actions · Answer 9

I got the answer:

select * from mytable

where class='something'

AND ISNULL(xyz,0) = CASE WHEN @abc = 1 THEN 1 ELSE ISNULL(xyz,0) END

Here @abc is the stored procedure parameter .

Thanks all for replying.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

a sample set and desired output will help to identify your actual scenario.

Emil B SSCertifiable Points: 5659 More actions · Answer 11

rexyrexin (3/18/2015)
I got the answer:
select * from mytable
where class='something'
AND ISNULL(xyz,0) = CASE WHEN @abc = 1 THEN 1 ELSE ISNULL(xyz,0) END
Here @abc is the stored procedure parameter .
Thanks all for replying.

If your @abc is other than 1 than the query will return all rows (where xyz is 1, 0 and null)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

twin.devil (3/18/2015)
a sample set and desired output will help to identify your actual scenario.

+1

A lot of confusion could have been avoided.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

rexyrexin (3/18/2015)
I got the answer:
select * from mytable
where class='something'
AND ISNULL(xyz,0) = CASE WHEN @abc = 1 THEN 1 ELSE ISNULL(xyz,0) END
Here @abc is the stored procedure parameter .
Thanks all for replying.

A slow answer as the predicates are non-sargable. Try this instead:

SELECT *

FROM mytable

WHERE class = 'something'

AND (

(@abc = 1 AND xyz = 1) OR (@abc IS NULL AND xyz IS NULL)

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden