best way to find nulls? where [MyColumn] is null

Question

best way to find nulls? where [MyColumn] is null

neotokyo

SSCertifiable

Points: 7239
More actions
October 22, 2007 at 2:03 pm

#180595

if you have a table with millions of records, and you have around 15 columns to look over...
what is the best way to find nulls??
i'm using the traditional: where [MyColumn] is null
is there another way i don't know about?
_________________________

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

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

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

To the best of my knowledge that really is the best way to do it. If the columns are indexed that should go fairly quickly though.

Kenneth Fisher

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]

neotokyo SSCertifiable Points: 7239 More actions · Answer 2

is there a question of = null instead of is null ??

i'm reading about the 2, and there seems to be some confusion.

just wondering.

for the most part i'm using is null. seems to be working ok.

_________________________

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

WHERE = NULL will not work unless you make a pretty uncommon (some consider it "dangerous") change to the ANSI NULL settings of the server. NULL is not "nothing" and you cannot compare to it. You must use ISNULL as already suggested. It's one of the powers of NULL expecially in Aggragate queries.

And, just in case... NULL should never be treated as a value of any kind... it should always be treated as an absence of data only. Again, it's doesn't mean "nothing", it means "unknown" or "no data" including the value "nothing". 🙂

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

rockzcm SSC Enthusiast Points: 174 More actions · Answer 4

I agree.

Null is an very interesting data type.

a=null

b=null

if a=b then 'Yes'

Else

'No'

You will expect to get No since you cannot compare two null values.

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 5

VaiydeyanathanVS

Hall of Fame

Points: 3053

October 24, 2007 at 3:30 am

#744831

http://www.craigsmullins.com/ssu_0999.htm

neotokyo SSCertifiable Points: 7239 More actions · Answer 6

Excellent! Thanks for all the good feedback. I'm going over some docs now about it.

_________________________

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

rockzcm (10/24/2007)
I agree.
Null is an very interesting data type.
a=null
b=null
if a=b then 'Yes'
Else
'No'
You will expect to get No since you cannot compare two null values.

Even more interesting... the code you have above isn't T-SQL and doesn't work in SQL Server... 😉

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

Pseudo code Jeff, Pseudo code.

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]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

Ah... got it... Looked more like VBS. Not sure why folks don't just write real code... doesn't take any longer 😛

DECLARE @a INT --Defaults to NULL on creation

DECLARE @b-2 INT --Defaults to NULL on creation

IF @a = @b-2

PRINT 'Yes'

ELSE

PRINT 'No'

And, I agree... shows how odd NULLs are... demonstrates that NULLs truly cannot be compared even to each other and that even NULL is not equal to NULL because NULL indicates "unknown"... brings up the old saying that "Null is not nothing".

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

...especially when you factor in that although Null is never equal to NULL, NULLs get aggregated as one data point (as that recent featured article pointed out). Kind of an interesting design choice when you compare it to all of the other handlings of NULL.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

I always thought the decision to agragate on NULLs made perfect sense. I can't think of the number of times I have needed a count of how many times a field was null.

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]

Derek Karpinski Ten Centuries Points: 1256 More actions · Answer 12

Nulls. Bane of my life. Especially when you have to do JOINs in a SELECT...

I don't understand them apart from they represent a "unknown" value. It's definetely more than trinary and one day I hope they will be outlawed...

Programmers don't understand them. But in many programming languages a NULL has a distinct value.. e.g. C#, where it is very usefult and can be tested

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Kenneth Fisher (10/24/2007)
I always thought the decision to agragate on NULLs made perfect sense. I can't think of the number of times I have needed a count of how many times a field was null.

No doubt it's practical..Just kind of went against their "Null is never equal to Null" mantra they apply. Of course - I suppose the converse is true as well ("Null is never not equal to Null"), so it's a choice to make. It's just kind of an interesting world to be able to quantify your unknowns....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Antares686 SSC Guru Points: 125444 More actions · Answer 14

I don't know. I am a programmer and never had an issue with understanding the logic of the knows and unknowns.

Best way I ever could think of to describe is a magician.

He takes two pennies and state he has only two pennies (let's call these bit datatype objects for fun) and places them in his hat on the table. He then does some movement and shows you his left hand with a penny in it (as a bit we will call this TRUE). He then shows you his right hand with no penny in it (as a bit we'll call this hand FALSE). Now he asks if the other penny is in his hat still. You might say yes (TRUE) as an educated guess based on the principal of the provided data but this is a magician, they could just show you no (FALSE) and then ask where has the penny gone. Well the fact is your are unsure so your answer is you don't know (we will call this NULL).

Now he might trick you say to you it is in one of his jacket pockets then ask if the other penny is in his left or right jacket pocket (this hopefully will expalin why NULL does not equal NULL), in both cases this you say I don't know, but you assume one or the other since he asked about both. The fact here is the penny exists somewhere but that existance may not be the same for both scenarios thus your I don't know although to you means you don't know the application of logic to the pockets means it could be in either one or the other. Thus that in itself tells you the left pockets outcome does not equal right pockets outcome as only one penny can exist. But with a magician they could both have a penny or both be empty even. Due to this fact they may or may not be the same result means you cannot evaluate the two as equal without more information and thus NULL can not definitively equal NULL until it becomes known, there is a large degree of uncertainty with evaulating a magicians penny location.