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
I feel ya man. NULL has always been a special case though. It's the bastard stepchild of values. Isn't technology fun?
Jeremy Falcon
-
Chris Maunder wrote:
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'.Either I've misunderstood your requirement, or that query isn't technically correct. The query will find the records in
TableOnewhich don't have any corresponding rows inTableTwo, and the records inTableOnewhich have at least one corresponding record inTableTwowhereStringColumnisNullor not equal to'value'. If there are multiple matching rows inTableTwo, then the rows fromTableOnewill be repeated. Based on the description, I'd have thought something like this would be a better fit:SELECT Count(1)
FROM TableOne
WHERE Not Exists
(
SELECT 1
FROM TableTwo
WHERE TableTwo.TableTwoID = TableOne.TableTwoID
And TableTwo.StringColumn = 'value'
)That finds all the rows in
TableOnewhich don't have a corresponding row inTableTwowithStringColumnset to'value'.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I think this version reads cleaner (and without the quirks), but I'd be willing to bet the non-sub select version would run quicker.
Jeremy Falcon
-
I think this version reads cleaner (and without the quirks), but I'd be willing to bet the non-sub select version would run quicker.
Jeremy Falcon
Jeremy Falcon wrote:
would run quicker
Indeed.
#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
-
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
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
-
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
-
Au contraire. It's the perfect language. Job security forever.
cheers Chris Maunder
LOL. Especially when you have your own company! ;P
#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
Quote:
Job security forever
Indeed. Writing script, backup,... It's boring but secure.
-
Quote:
Job security forever
Indeed. Writing script, backup,... It's boring but secure.
So is prostitution... but ya know... :~
Jeremy Falcon
-
I think this version reads cleaner (and without the quirks), but I'd be willing to bet the non-sub select version would run quicker.
Jeremy Falcon
Assuming SQL Server, I'll take that bet. NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server at EXPLAIN EXTENDED[^] Left outer join vs NOT EXISTS | SQL in the Wild[^] There are always exceptions, but
not existsis generally better optimized.Wrong is evil and must be defeated. - Jeff Ello
-
Null most certainly does not equal a valid string value, right? I never realised SQL could be so Javaesque ;)
cheers Chris Maunder
It gets Dali-esque as well from time to time...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
-
So is prostitution... but ya know... :~
Jeremy Falcon
Boring?
Wrong is evil and must be defeated. - Jeff Ello
-
Boring?
Wrong is evil and must be defeated. - Jeff Ello
Some are... so I'm told. :rolleyes:
Jeremy Falcon
-
Boring?
Wrong is evil and must be defeated. - Jeff Ello
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
-
So is prostitution... but ya know... :~
Jeremy Falcon
-
So wait, you're saying I can't be a prostitute? Why? Because I'm a guy? That's discrimination.
Jeremy Falcon
-
So wait, you're saying I can't be a prostitute? Why? Because I'm a guy? That's discrimination.
Jeremy Falcon
-
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
-
It gets Dali-esque as well from time to time...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
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.
-
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.
-
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...
