Does NULL <> 'string'?
-
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
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...
-
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:
SET ANSI_NULLS (Transact-SQL) | Microsoft Docs[^]:
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
-
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 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.
-
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
-
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
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.
-
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#.
...except there's an "or" in there: logically, it shouldn't matter: if it's null, at least one of the conditions is true. I have a feeling that "null=string" evaluates to null, and then "field is null" evaluates to Boolean, then "boolean or null" evaluates to null, which is why it doesn't work. But that's somewhat abstruse. Which server is this: is it SQL Server or MySQL... or perhaps something else?
-
...except there's an "or" in there: logically, it shouldn't matter: if it's null, at least one of the conditions is true. I have a feeling that "null=string" evaluates to null, and then "field is null" evaluates to Boolean, then "boolean or null" evaluates to null, which is why it doesn't work. But that's somewhat abstruse. Which server is this: is it SQL Server or MySQL... or perhaps something else?
-
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
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.
-
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
-
OriginalGriff wrote:
Dali-esque
Surrealistic? or womenizer? :laugh: :laugh: :laugh: :laugh:
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.
Nelek wrote:
womenizer?
My experience is that SQL lacks, er, discrimination in who it molests.
Software Zen:
delete this; -
...except there's an "or" in there: logically, it shouldn't matter: if it's null, at least one of the conditions is true. I have a feeling that "null=string" evaluates to null, and then "field is null" evaluates to Boolean, then "boolean or null" evaluates to null, which is why it doesn't work. But that's somewhat abstruse. Which server is this: is it SQL Server or MySQL... or perhaps something else?
Dan Sutton wrote:
I have a feeling that "null=string" evaluates to null, and then "field is null" evaluates to Boolean, then "boolean or null" evaluates to null, which is why it doesn't work.
Pretty much, but it depends on the operator and the boolean value. If it's possible to short-circuit the operator, it doesn't matter if one condition is
Null.AND | True | False | Null
True | True | False | Null
False | False | False | False
Null | Null | False | NullOR | True | False | Null
True | True | True | True
False | True | False | Null
Null | True | Null | NullIt's sometimes easier to think of
Nullas "unknown". :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
But the IS NULL check is made on TableTwo.TableTwoID While string compare is made with TableTwo.StringColumn So I see no incorrect handling, but of course it is not very intuitiv.
Yeah - you're right. Hadn't had coffee yet. LOL!
-
Dan Sutton wrote:
I have a feeling that "null=string" evaluates to null, and then "field is null" evaluates to Boolean, then "boolean or null" evaluates to null, which is why it doesn't work.
Pretty much, but it depends on the operator and the boolean value. If it's possible to short-circuit the operator, it doesn't matter if one condition is
Null.AND | True | False | Null
True | True | False | Null
False | False | False | False
Null | Null | False | NullOR | True | False | Null
True | True | True | True
False | True | False | Null
Null | True | Null | NullIt's sometimes easier to think of
Nullas "unknown". :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I know: I failed to see first time around that the comparisons were made on separate columns. Coffee. It's a mandatory component first thing in the morning. Of course, not having had any, I failed to realise that, too!
-
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
