Does NULL <> 'string'?

Chris Maunder

You'd think so. Except in SQL We had a query:

Select count(*)
From TableOne

which 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 TableOne which, when joined with TableTwo, either have no corresponding TableTwo row or the corresponding TableTwo row is not 'value'. TableTwo.StringColumn is 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.StringColumn is null. So one needs to use

IsNull(TableTwo.StringColumn, '') <> 'value'

to get the correct result.

cheers Chris Maunder

Lost User

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#.

Chris Maunder

Null most certainly does not equal a valid string value, right? I never realised SQL could be so Javaesque ;)

cheers Chris Maunder

Richard Deeming

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.StringColumn will also be Null.)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Chris Maunder

Richard Deeming wrote:

It's simple three-valued Boolean logic

True, False, and Surprise.

cheers Chris Maunder

Richard Deeming

Chris Maunder wrote:

We're trying to find the number of records in TableOne which, when joined with TableTwo, either have no corresponding TableTwo row or the corresponding TableTwo row is not 'value'.

Either I've misunderstood your requirement, or that query isn't technically correct. The query will find the records in TableOne which don't have any corresponding rows in TableTwo, and the records in TableOne which have at least one corresponding record in TableTwo where StringColumn is Null or not equal to 'value'. If there are multiple matching rows in TableTwo, then the rows from TableOne will 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 TableOne which don't have a corresponding row in TableTwo with StringColumn set to 'value'.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Richard Deeming

True, False, and FileNotFound. :-D What Is Truth? - The Daily WTF[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Jeremy Falcon

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

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

TheGreatAndPowerfulOz

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

TheGreatAndPowerfulOz

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

Chris Maunder

Au contraire. It's the perfect language. Job security forever.

cheers Chris Maunder

TheGreatAndPowerfulOz

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

Erick Mattew

Quote:

Job security forever

Indeed. Writing script, backup,... It's boring but secure.

Jeremy Falcon

So is prostitution... but ya know... :~

Jeremy Falcon

Jorgen Andersson

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 exists is generally better optimized.

Wrong is evil and must be defeated. - Jeff Ello

OriginalGriff

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!

Jorgen Andersson

Boring?

Wrong is evil and must be defeated. - Jeff Ello

Jeremy Falcon

Some are... so I'm told. :rolleyes:

Jeremy Falcon

TheGreatAndPowerfulOz

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