|
This proves SQL is not a real programming language.
#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
|
|
|
|
|
Au contraire. It's the perfect language. Job security forever.
cheers
Chris Maunder
|
|
|
|
|
LOL. Especially when you have your own company!
#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
|
|
|
|
|
Quote: Job security forever Indeed. Writing script, backup,... It's boring but secure.
|
|
|
|
|
So is prostitution... but ya know...
Jeremy Falcon
|
|
|
|
|
|
Some are... so I'm told.
Jeremy Falcon
|
|
|
|
|
They don't all "enjoy" it. It's a job. Indeed, prostitutes go same-sex for their personal one-on-ones because opposite-sex has lost all appeal due to the "job".
#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
|
|
|
|
|
Sorry, You failed the interview. He'll call you next time.
|
|
|
|
|
So wait, you're saying I can't be a prostitute? Why? Because I'm a guy? That's discrimination.
Jeremy Falcon
|
|
|
|
|
Are you saying, Sql Dev's = prostitute?
|
|
|
|
|
Yes. Everyone knows you have go through prostitute training to get certified. You just finding this out? You may wanna talk to your MS rep.
Jeremy Falcon
|
|
|
|
|
Guys... this is not the soapbox
M.D.V.
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
It's also not the lounge.
#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
|
|
|
|
|
Like everything else in programming, it depends. In this case it depends on the SQL ansi_nulls setting. If this is a statement inside a SQL stored procedure, view, etc the ansi_nulls setting is burned into the object when it is created. If this is an ad-hoc statement then the ansi_nulls setting in effect for the SQL connection the statement is executed against is used.
If you want null <> 'value' to return true, you could just set ansi_nulls off. Not that I would recommend doing that...
|
|
|
|
|
txmrm wrote: Not that I would recommend doing that...
Especially as it's deprecated, and likely to be removed:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
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
|
|
|
|