Gossamer Forum
Quote Reply
A query
If I use the following:

SELECT * FROM Table WHERE Col1 != '2' AND Col2 = '2'

...and I have one row:

Col1 = NULL
Col2 = 2

....why is nothing being selected?.....I get no errors from DBI->errstr

Last edited by:

Paul: Aug 16, 2002, 3:18 AM
Quote Reply
Re: [Paul] A query In reply to
Paul,
Your select fails because you are searching for a numeric value on a NULL field.
The NULL value means no data, and is different from values such as 0 for numeric types or the empty string for string types.
If you add a value to your NULL field then it will work.
Check your mySQL docs.
3.3.4.6 Working with NULL Values
6.1.1.4 NULL Values
A.5.3 Problems with NULL Values

Bob
http://totallyfreeads.com
Quote Reply
Re: [lanerj] A query In reply to
Are you saying that mysql doesn't understand that NULL isn't equal to 2?....that seems a bit stupid to me.

Last edited by:

Paul: Aug 16, 2002, 7:05 AM
Quote Reply
Re: [Paul] A query In reply to
Yep it is stupid.
If you want to find NULL values then you have to use the operators IS NULL and IS NOT NULL.
You cannot use arithmetic comparison operators to find Null values.
RTFM.

Bob
http://totallyfreeads.com

Last edited by:

lanerj: Aug 16, 2002, 7:53 AM
Quote Reply
Re: [lanerj] A query In reply to
>>
If you want to find NULL values then you have to use the operators IS NULL and IS NOT NULL.
<<

Well yeah I know that but I'm not looking for NULL values specifically, I'm looking for anything that isn't two so I would have thought != 2 would have done what I expected, again, how stupid.

Last edited by:

Paul: Aug 16, 2002, 8:15 AM
Quote Reply
Re: [Paul] A query In reply to
(COL1 <> '2')

<> means IS NOT
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Aug 16, 2002, 9:06 AM
Quote Reply
Re: [Stealth] A query In reply to
I know, I tried that too, neither != or <> worked. Always returned 0 rows.

I ended up using a different approach in the end.

Last edited by:

Paul: Aug 16, 2002, 9:07 AM
Quote Reply
Re: [Paul] A query In reply to
It's SQL theory. NULL does not mean empty, or 0. It means unknown.

If you have 'Col1 != 2' and compare a row that has Col1 == NULL, MySQL will return FALSE as Col1 is unknown (it could be 2, it could be anything in theory).

Have a read through:

http://www.reviewnet.net/...rticles/a3_feb02.htm

for more info.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] A query In reply to
Thanks, I'll have a read

Quote:
This three-valued logic is the source of much frustration for beginners, and still trips the more experienced for a moment, now and then.

Erm, I'd like to think I fit into the second part of that sentence Cool
Quote Reply
Re: [Alex] A query In reply to
Hmm it looks like I was thinking in human logic and not in "sql logic" .....it looks like I would have needed to use:

SELECT FROM Table Where Col <> 2 OR Col IS NULL

NULL = Unknown seems strange to me, I would have expected NULL to means "nothing" rather than "missing" or "unknown"


Thanks for that link, it was really specific to my question....did you just find that or do you have it bookmarked?

Last edited by:

Paul: Aug 16, 2002, 12:49 PM
Quote Reply
Re: [Paul] A query In reply to
I knew what I was looking for, just wanted a better explanation.. Second result on Google for 'NULL SQL unknown'. =)

Cheers,

Alex
--
Gossamer Threads Inc.