SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 if 1=1 then why does null != null?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

nic
Posting Yak Master

209 Posts

Posted - 07/16/2004 :  15:21:59  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 07/16/2004 :  15:39:24  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 07/16/2004 15:42:09
Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 07/16/2004 :  15:39:29  Show Profile  Reply with Quote
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 - 07/16/2004 :  16:10:08  Show Profile  Reply with Quote
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

USA
3464 Posts

Posted - 07/16/2004 :  16:15:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/16/2004 :  17:56:29  Show Profile  Reply with Quote
I hate NULLs. I avoid them when possible to try and avoid odd results.


-ec
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/17/2004 :  04:00:19  Show Profile  Reply with Quote
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

USA
4184 Posts

Posted - 07/17/2004 :  09:36:47  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 07/17/2004 :  10:19:18  Show Profile  Reply with Quote
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.

Edited by - Arnold Fribble on 07/17/2004 10:19:50
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/17/2004 :  11:09:24  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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

USA
3464 Posts

Posted - 07/17/2004 :  11:20:23  Show Profile  Reply with Quote
And yet... noone has corrected the CASE statement I posted.

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 07/17/2004 :  11:38:38  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 07/17/2004 :  12:26:31  Show Profile  Reply with Quote
Might work with one of those silly SET AnsiNull thingamajigs

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/17/2004 :  12:51:10  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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'Coño',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

United Kingdom
1961 Posts

Posted - 07/17/2004 :  13:08:39  Show Profile  Reply with Quote
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...

Edited by - Arnold Fribble on 07/17/2004 13:11:47
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

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

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 07/17/2004 :  13:27:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/17/2004 :  14:27:14  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 07/17/2004 :  14:35:58  Show Profile  Reply with Quote
pass the test?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/17/2004 :  15:08:41  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 07/17/2004 :  15:35:37  Show Profile  Reply with Quote
Sorry, but I won't post it. It's not the potential reusability, more that it would make the foregoing remarks identifiable.

Edited by - Arnold Fribble on 07/17/2004 15:36:53
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000