Does NULL <> 'string'?
-
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
-
Yeah - you're right. Hadn't had coffee yet. LOL!
-
It's simple three-valued Boolean logic - what's the problem?! :laugh:
Chris Maunder wrote:
IsNull(TableTwo.StringColumn, '') <> 'value'
Except that's not SARGabale.
WHERE
TableTwo.StringColumn Is Null
Or
TableTwo.StringColumn != 'value'(You can remove
TableTwo.TableTwoID Is Null, because if that's true,TableTwo.StringColumnwill also beNull.)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Only if you're anal about standards-compliance. ;P
sql - Should I use != or <> for not equal in TSQL? - Stack Overflow[^]:
Databases that support both
!=and<>:- MySQL 5.1
- PostgreSQL 8.3
- SQLite
- Oracle 10g
- Microsoft SQL Server 2000/2005/2008/2012/2016
- IBM Informix Dynamic Server 10
- InterBase/Firebird
- Apache Derby 10.6
- Sybase Adaptive Server Enterprise 11.0
Databases that support the ANSI standard operator, exclusively:
- IBM DB2 UDB 9.5
- Microsoft Access 2010
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Only if you're anal about standards-compliance. ;P
sql - Should I use != or <> for not equal in TSQL? - Stack Overflow[^]:
Databases that support both
!=and<>:- MySQL 5.1
- PostgreSQL 8.3
- SQLite
- Oracle 10g
- Microsoft SQL Server 2000/2005/2008/2012/2016
- IBM Informix Dynamic Server 10
- InterBase/Firebird
- Apache Derby 10.6
- Sybase Adaptive Server Enterprise 11.0
Databases that support the ANSI standard operator, exclusively:
- IBM DB2 UDB 9.5
- Microsoft Access 2010
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
What the hell... Thank you for the info. Unfortunatelly I have to work mostly with interbase (we are on the way to replace it by MSSQL)and with Interbase until now I thought "!=" is not possible. Made just a short test, and yes also IB accpets "!=" :-O :laugh: [Edit] BTW: Have some Problems to seriously translate "if you're anal compliance" :confused: :laugh:
-
What the hell... Thank you for the info. Unfortunatelly I have to work mostly with interbase (we are on the way to replace it by MSSQL)and with Interbase until now I thought "!=" is not possible. Made just a short test, and yes also IB accpets "!=" :-O :laugh: [Edit] BTW: Have some Problems to seriously translate "if you're anal compliance" :confused: :laugh:
0x01AA wrote:
BTW: Have some Problems to seriously translate "if you're anal about compliance"
anal - Wiktionary[^] - definition #3: of a person, compulsive and stubborn, obsessed with neatness and accuracy Synonyms: fussy, pernickety, picky
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
0x01AA wrote:
BTW: Have some Problems to seriously translate "if you're anal about compliance"
anal - Wiktionary[^] - definition #3: of a person, compulsive and stubborn, obsessed with neatness and accuracy Synonyms: fussy, pernickety, picky
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You'd think so. Except in SQL We had a query:
Select count(*)
From TableOnewhich returned, say, 500,000 records. Next we added
Select count(*)
From TableOne
Left Join TableTwo On TableTwo.TableTwoID = TableOne.TableTwoID
Where TableTwo.TableTwoID is null or TableTwo.StringColumn <> 'value'We're trying to find the number of records in
TableOnewhich, when joined withTableTwo, either have no corresponding TableTwo row or the correspondingTableTworow is not 'value'.TableTwo.StringColumnis nullable. The result? Adding the join resulted in 25K records. It should have been over 490K records. The issue?TableTwo.StringColumn <> 'value'
This comparison returns false if
TableTwo.StringColumnis null. So one needs to useIsNull(TableTwo.StringColumn, '') <> 'value'
to get the correct result.
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++.
-
You'd think so. Except in SQL We had a query:
Select count(*)
From TableOnewhich returned, say, 500,000 records. Next we added
Select count(*)
From TableOne
Left Join TableTwo On TableTwo.TableTwoID = TableOne.TableTwoID
Where TableTwo.TableTwoID is null or TableTwo.StringColumn <> 'value'We're trying to find the number of records in
TableOnewhich, when joined withTableTwo, either have no corresponding TableTwo row or the correspondingTableTworow is not 'value'.TableTwo.StringColumnis nullable. The result? Adding the join resulted in 25K records. It should have been over 490K records. The issue?TableTwo.StringColumn <> 'value'
This comparison returns false if
TableTwo.StringColumnis null. So one needs to useIsNull(TableTwo.StringColumn, '') <> 'value'
to get the correct result.
cheers Chris Maunder
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 :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
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 :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
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
-
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
-
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
-
Yes, and it is pretty correct. NULL is not defined and can therefore not be compared to a value. Also not for nuallable type in c#.
Exactly - in Codd's original work (which defined Relational Database Management Systems), this is referred to as null progagation. Interesting, in The Relational Model for Database Management Systems V2, he introduced two null values one indicates the value is unknown and inapplicable (like the null behaviour you described), and the other indicating the value is unknown and applicable (which would give the behaviour Chris was expecting). Sadly, to the best of my knowledge, no-one bothered pursuing any of the good ideas in there.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
You'd think so. Except in SQL We had a query:
Select count(*)
From TableOnewhich returned, say, 500,000 records. Next we added
Select count(*)
From TableOne
Left Join TableTwo On TableTwo.TableTwoID = TableOne.TableTwoID
Where TableTwo.TableTwoID is null or TableTwo.StringColumn <> 'value'We're trying to find the number of records in
TableOnewhich, when joined withTableTwo, either have no corresponding TableTwo row or the correspondingTableTworow is not 'value'.TableTwo.StringColumnis nullable. The result? Adding the join resulted in 25K records. It should have been over 490K records. The issue?TableTwo.StringColumn <> 'value'
This comparison returns false if
TableTwo.StringColumnis null. So one needs to useIsNull(TableTwo.StringColumn, '') <> 'value'
to get the correct result.
cheers Chris Maunder
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
IsNullfunction)."If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
You'd think so. Except in SQL We had a query:
Select count(*)
From TableOnewhich returned, say, 500,000 records. Next we added
Select count(*)
From TableOne
Left Join TableTwo On TableTwo.TableTwoID = TableOne.TableTwoID
Where TableTwo.TableTwoID is null or TableTwo.StringColumn <> 'value'We're trying to find the number of records in
TableOnewhich, when joined withTableTwo, either have no corresponding TableTwo row or the correspondingTableTworow is not 'value'.TableTwo.StringColumnis nullable. The result? Adding the join resulted in 25K records. It should have been over 490K records. The issue?TableTwo.StringColumn <> 'value'
This comparison returns false if
TableTwo.StringColumnis null. So one needs to useIsNull(TableTwo.StringColumn, '') <> 'value'
to get the correct result.
cheers Chris Maunder
In the end I can feel with you. I just entered the trap with NULL values _one more again_ :^) :-D SELECT xyz FROM Tbl1 LEFT JOIN Tbl2 ON Tbl2.Tbl1_ID = Tbl1.ID WHERE Tbl1.IntValue <> Tbl2.IntValue Where in both tables NULL Values are allowed for the field IntValue. S*t happens :doh:
-
You'd think so. Except in SQL We had a query:
Select count(*)
From TableOnewhich returned, say, 500,000 records. Next we added
Select count(*)
From TableOne
Left Join TableTwo On TableTwo.TableTwoID = TableOne.TableTwoID
Where TableTwo.TableTwoID is null or TableTwo.StringColumn <> 'value'We're trying to find the number of records in
TableOnewhich, when joined withTableTwo, either have no corresponding TableTwo row or the correspondingTableTworow is not 'value'.TableTwo.StringColumnis nullable. The result? Adding the join resulted in 25K records. It should have been over 490K records. The issue?TableTwo.StringColumn <> 'value'
This comparison returns false if
TableTwo.StringColumnis null. So one needs to useIsNull(TableTwo.StringColumn, '') <> 'value'
to get the correct result.
cheers Chris Maunder
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
