Does NULL <> 'string'?

Kirk 10389821

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.

Chris Maunder

I've learned it many time. My brain, however, refuses to store it in long term memory.

cheers Chris Maunder

Gary Wheeler

Nelek wrote:

womenizer?

My experience is that SQL lacks, er, discrimination in who it molests.

Software Zen: delete this;

Richard Deeming

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

OR | True | False | Null

It's sometimes easier to think of Null as "unknown". :)

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

Member_5893260

Yeah - you're right. Hadn't had coffee yet. LOL!

Member_5893260

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!

Richard Deeming

obermd wrote:

The SQL standards don't define how NULL should behave.

Yes they do. SQL-92 specifically states:

SQL-92, section 8.2[^]:

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

Lost User

:laugh:

Lost User

And avoid "!=" in SQL :laugh:

Richard Deeming

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

Lost User

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:

Richard Deeming

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

Lost User

Thank you for help me learning English. What such a insignifican word like "about" can change everything :laugh: Thank you Bruno

hpcoder2

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

Lost User

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

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

TheGreatAndPowerfulOz

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

TheGreatAndPowerfulOz

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

Rob Grainger

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.

Rob Grainger

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 IsNull function).

"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.