Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 if 1=1 then why does null != null?

Author  Topic 

nic
Posting Yak Master

209 Posts

Posted - 2004-07-16 : 15:21:59
I have a procedure used for reports where I use the coalesce statement to see if a value was provided. If no value was provided, I do not include the column in the where clause. This gets me around the scenario of having to use dynamic sql (http://www.sqlteam.com/item.asp?ItemID=2077). BUT I just noticed if the field in question contains a null value, the COALESCE statement fails.

if myField contains a null value, the record is not returned.


select * From myTable
where
myField = COALESCE(@field, myField)


I would think no matter what was in the field column, field = field would always be true. just as 1=1, you would think null=null, but that isn't true. If this is the case, I don't see any way to avoid using dynamic sql. hmmmmmm.



create table myTable (
tableID int null,
myField varchar(10) null
)

insert into myTable Values (1,null)

--declare @field varchar(10)
--set @field = null

--returns 1 record
select * from myTable

--returns 1 record
select * from myTable where tableID = tableID

-- returns nothing??
Select * from myTable where myField = myField

drop table myTable



Nic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-16 : 15:39:24
Question for you, Nic:

True or False: what I earn per hour = What Brett earns per hour

What would your answer to that question be, if you had to answer it right now? you can't say that it's true, since you don't know our salaries. To you, both of our salaries equal Null -- unknown. You can't even really say that it's false, either -- all you really determine for sure is that the answer to that question is "unknown"; certainly not a 100% positive TRUE.

Remember, Null = unknown

so is Null = Null, or 0 =Null, or Null = anything? the answer: Unknown !

however, if YOUR particular use of Null has a meaning -- i.e., for "Termination Date" you might say Null means someone who has not terminated -- then in your implementation of a query you might wish to equate a Null with a Null. But it would be illogical for SQL Server to make that assumption, based on the reasons I gave you. Thus, SQL requires that you need to explicitly tell it you wish for that comparision to return true either with ANSI NULLS OFF or by saying:

(A = B) or (A is null AND B is null)

does that help a little?

- Jeff
Go to Top of Page

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-16 : 15:39:29
Not sure about performance, but you could:

select * From myTable
where
COALESCE(myField, 1) = COALESCE(@field, myField, 1) -- data type matching needed of course
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-16 : 16:10:08
You get paid?

Nope...don't like it....

Just because you don't know something doesn't mean NULL

I like the temrination thing...

For an employee who is currently working there does not exists a termination date for their current job.

If something doesn't even exists, it can't be comparred to anything....not even its self, because it doesn't exists.

Null is not really data, but is often confused as a type of data...which it's not....becasue...well I hope get it....



Brett

8-)
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-16 : 16:15:38
Here's one that I like. What's wrong with this?

CASE ColA WHEN NULL THEN 'ColA is NULL' ELSE 'ColA is NOT NULL' END

always returns 'ColA is NOT NULL'

Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-16 : 17:56:29
I hate NULLs. I avoid them when possible to try and avoid odd results.


-ec
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 04:00:19
I like Celko's definition of NULL:

The hair colour of a bald man.

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 09:36:47
He better be completely bald, or maybe that's the point. hmmm

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-17 : 10:19:18
quote:
Originally posted by derrickleggett

He better be completely bald, or maybe that's the point. hmmm


Remember the Disney version of Aladdin? Imagine the Genie without a topknot. And less blue.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 11:09:24
Ahhhh, everything now becomes clear. I feel the horizon.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-17 : 11:20:23
And yet... noone has corrected the CASE statement I posted.

Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-17 : 11:38:38
Oh alright then...
You can't use CASE with NULL like that because it does the equivalent of an equality check. You'd have to do something like:

CASE WHEN ColA IS NULL THEN 'ColA is NULL' ELSE 'ColA is NOT NULL' END
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 12:26:31
Might work with one of those silly SET AnsiNull thingamajigs

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 12:51:10
Well, I'll be twinkered. Yep, it works alright. Syntax is a kewl thing to know ya know. So are tests. I like tests. They seem to prove things years of babble just ummmm, well, ummmmmmmm babbles about.



IF (SELECT OBJECT_ID('Northwind..djl_test')) IS NOT NULL
BEGIN
DROP TABLE djl_test
END

--Create the table to fix this awful nightmare of a situation
CREATE TABLE djl_test(
int1 INT,
int2 INT,
value NVARCHAR(55),
int3 INT)

INSERT djl_test(int1, int2, value, int3)
SELECT 1,2,'Here''s the problem',4
UNION ALL
SELECT 1,3,'What????',5
UNION ALL
SELECT 1,5,N'Coo',99
UNION ALL
SELECT 2,5,NULL,99

SELECT * FROM djl_test

--Testing. If this was a real emergency, you'd be dead so don't worry about it.
SELECT
int1,
int2,
value,
CASE
WHEN value IS NULL THEN 'value is NULL'
ELSE 'value is NOT NULL'
END AS value_test
FROM djl_test

--Clean up after yourself. gheesh
DROP TABLE djl_test



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-17 : 13:08:39
quote:
I like tests. They seem to prove things years of babble just ummmm, well, ummmmmmmm babbles about.



Don't talk to me about tests. I could cry, I really could.
We interviewed this week. Seven candidates. Simple SQL test. Well, we thought so. All of them failed produce a solution. I simply could not fathom the thought processes of at least two of them.
I'd post the test, but you never know who might be lurking...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 13:11:34
Are you worried your answer isn't the right one then Arnold?!

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-17 : 13:27:34
If only. One of them, it was like that old, much misattributed, Wolfgang Pauli quote, "This isn't right, it isn't even wrong."
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-17 : 14:27:14
quote:
Originally posted by Arnold Fribble

quote:
I like tests. They seem to prove things years of babble just ummmm, well, ummmmmmmm babbles about.



Don't talk to me about tests. I could cry, I really could.
We interviewed this week. Seven candidates. Simple SQL test. Well, we thought so. All of them failed produce a solution. I simply could not fathom the thought processes of at least two of them.
I'd post the test, but you never know who might be lurking...




post the test!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 14:35:58
pass the test?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 15:08:41
Yes please. I would like to see it so I know if I ever interview with you. lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-17 : 15:35:37
Sorry, but I won't post it. It's not the potential reusability, more that it would make the foregoing remarks identifiable.
Go to Top of Page
    Next Page

- Advertisement -