|
It may be weird, but it's nothing new...
I learned SQL on mainframe, and the first thing we had to memorize about NULL is that it can not stand of either side of any of the comparison operators (=, <, >, <>)...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
The SQL standards don't define how NULL should behave. What you found is accurate for one SQL dialect but may not be accurate for another. This is actually one of the biggest challenges when changing RDBMS vendors.
|
|
|
|
|
obermd wrote: The SQL standards don't define how NULL should behave.
Yes they do. SQL-92 specifically states:
If XV or YV is the null value, then "X <comp op> Y" is un-known.
That's the standard behaviour with SET ANSI_NULLS ON (the default).
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Total guess here, but I wonder if the SQL engine errors on the side of caution. Something like:
"I don't have a string to compare to. Thus, I can't say with certainty it is or is not 'value', so I'll return false."
But yeah, I think that has gotten us all at one time or another when doing inequality in a query.
I also wonder if it varies with different implementations of SQL. But not enough to, you know, test it or anything.
|
|
|
|
|
You stumbled on the NULL value special case. NOTHING can be compared to NULL.
In fact NULL <> NULL... ISNULL() is the correct way, or (X IS NULL)
It makes for difficult code to write with dynamic where clauses on nullable fields.
I have seen SOME DBs that they treat '' as NULL
and it is worse with a varchar field that trims trailing spaces, and someone stores a space.
This is BY DESIGN as explained, and part of the "CALCULUS" of the system.
ALSO (1 = NULL) is NOT false... It is NULL
That prevents inverting the logic to "NOT (1 = NULL)"
Learn that ONCE, learn it well, and life becomes easier.
|
|
|
|
|
I've learned it many time.
My brain, however, refuses to store it in long term memory.
cheers
Chris Maunder
|
|
|
|
|
Actually, the same sort of thing happens in floating point arithmetic, in pretty much any language under the sun. The values NaN (yes, there's more than one type of NaN) always return false when compared with any other number. So both NaN==x and NaN != x are false. etc., etc.
This has bitten me in the arse a number of times, and I work in C++.
|
|
|
|
|
Chris Maunder wrote: to get the correct result.
The other result was also correct, just not the one you wanted. The behaviour is logical.
Your last example would be very VB-ish, where a "NULL" value is treated like an empty string. In the database-world, an empty value does not mean an empty string.
..and it is not something recent, is it? :p
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: The other result was also correct, just not the one you wanted
This is exactly why I love programming.
cheers
Chris Maunder
|
|
|
|
|
My sarcasm meter just broke.
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
I prefer the idiom...
(TableTwo.StringColumn <> 'value') or TableTwo.StringColumn is null
I find it a bit more readable (never really liked the name of the IsNull function).
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
|
It's either col equals null meaning a field with no value or you've got an epmty string
you can use
Select *
From Table
Where (col is null or col = '')
or IsNull method to find epty strngs and null values
_______________________________________________________________________________________________
Alec
programming[^] expert
|
|
|
|
|
A guy who I used to work with had two infamous sayings. Sadly, he passed away a few years ago and left a huge gap here, but his wit and wisdom did not go to the grave with him. Two of the best:
~ If you're not causing production problems, you're not doing anything.
(supervisor-types weren't fans of that one)
~ IIWD2WISBD2U
* If it was difficult to write, it should be difficult to understand.
[This, coming from one of two Computer Engineering folks, with everyone else in the shop holding DP/business degrees.]
(Lazy developers who couldn't program their way out of a paper bag with a hole in it (or who had yet to master Google) were not particularly fond of that one)
|
|
|
|
|
"Plans are like fishing nets - the more complicated they are, the more holes you will find in them."
|
|
|
|
|
"I am root" - Displayed by a virus that a student at IPFW wrote and put on the school's network. It would constantly pop up in modal dialogs, rendering the computer unusable.
AFAIK he got expelled for the incident. He was to graduate that year as well.
What do you get when you cross a joke with a rhetorical question?
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
Do questions with multiple question marks annoy you???
|
|
|
|
|
A company I used to work for had a GUI program that ran on windows 3.1 for editing labels..
The support manager used to say "it was nearer WYSICUB than WISIWYG"
WYSICUB = What you see is complete utter b****x
Thanks,
Robin.
|
|
|
|
|
I think I work there. Everything at my current job -- ESPECIALLY the brand new systems are WYSICUB.
I'm going to start using that. Thanks for the laugh!
|
|
|
|
|
I just realized I missed another one. We used to use PEBKAC. I just came upon PICNIC. Problem in Chair, not in Computer. Much nicer to say someone is a real PICNIC to work with!
|
|
|
|
|
From long ago...WMTSU means 'we're making this stuff up'.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Are you sure the "S" stands for "stuff"? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Two spring readily to mind...
OWNER: Old W*nker Needs Early Retirement.
And a favourite from my photographic days, working in retouch labs:
PHOTOGRAPHER: Pig Headed Old Tosser Only Gets Respect After Proving He's Enormously Rich!
Danny
|
|
|
|
|
MIL-T-FD41
Make It Like The Flippin' Drawing For Once
(From the days of pre-computer-ubiquity.)
|
|
|
|
|
DILLIGAF
Do I Look Like I Give A F...inished this sentence yet?
LOMBARD
Lots Of Money But A Real D...head.
I have, in the past, referred to certain co-workers as "Errol Flynn" ... basically they f...k everything they touch.
www.CADbloke.com
The Broadcast Systems Documentation SYSTEM
"The mass of men lead lives of quiet desperation"
-Zen & the Art of Motorcycle Maintenance
|
|
|
|
|
I am the DSJB: Departmental Sh!t-Job(*) Boy.
(*) Source control wizard (Visual SourceSafe, no less), build server admin, build process author, ...
Software Zen: delete this;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
