Computer Jockeys: NULL

[AZRedhawk44]

Working with some SQL this morning and came across a situation that always stymies me:

NULL has no value... not zero, not an empty string... it is non-existence.

NULL ≠ NULL

I had do do a

Code: [Select]

WHERE isnull(t1.somecolumn, '') <> isnull(t2.somecolumn, '')
to work around it.

Is this the same across Oracle, MySQL, DB2 and other RDBMS systems?  What about C++, Java, VB?  I've never had to deal with interpreting NULL in compiled programming environments or in a non MS-SQL environment.  Just curious if everyone handles it the same, or if NULL can = NULL in another environment.

[BrokenPaw]

In some environments, (C and C++ come to mind) NULL is a discrete value, and may even be given a distinct numeric value (like 0 in C++). 

Thus in C++, you can (technically) get away with:

Code: [Select]

char *foo = 0;
char *bar = NULL;

if (foo == bar) // evaluates as true
    doSomething();
even though it's horrible form.

I can't speak for Java; it's a toy language, and will never catch on, so it's not worth considering.  And the less said about VB, the better.  :)

In SQL, NULL might have been better designated as UNDEFINED, because there's no implicit idea in a programmer's head that UNDEFINED == UNDEFINED, but because NULL implies 0, it's not an illogical leap to assume that NULL == NULL.  Probably NULL was used because it's easier and faster to type than UNDEFINED.

[Nick1911]

Nulls can be compared directly in java:

if(null == null) {
      //Always true
      }

You can't really directly compare in Oracle, though.

SELECT 'FOO' FROM DUAL WHERE NULL = NULL;

Returns nothing - no match.  You have to use the "IS NULL" operator in oracle.

SELECT 'FOO' FROM DUAL WHERE NULL IS NULL;

[Tallpine]

In some environments, (C and C++ come to mind) NULL is a discrete value, and may even be given a distinct numeric value (like 0 in C++). 

Yeah, in that case NULL is more of a syntactical convenience to convey to anyone reading the source code that 0 or whatever value assigned to "NULL" is meaningless, or a non-valid value.

void* p = NULL;
p = new(something);
if ( p <> NULL )
{
      p.value = X;
}

[GigaBuist]

You have to use the "IS NULL" operator in oracle.

T-SQL (SQL Server and Sybase) is the same.  Pretty sure it's ANSI standard.

[CNYCacher]

Yes, NULL does not equal anything in SQL, including NULL, and thinking of it as UNDEFINED is a good way to wrap your brain around it.  Another way to think about it is to think that there is a value but we don't know what it is.

In programming languages, null usually has a different meaning, and is often equal to zero when caste as a integer.

[GigaBuist]

I'm bored so I'll expand on this.

In C and C++ there's really no concept of NULL or null in the core language definition.  Plenty of libraries provide a #const definition of it that sets it to 0 though.  The two values are completely interchangeable as the text "NULL" just gets turned into 0 during the preprocessing phase.

In VB it isn't 'null' or 'NULL' -- the keyword there is 'Nothing'  which has always annoyed me but whatever.  However it only applies to actual objects.  The primitives like Integer, Double, String, etc. cannot ever be Nothing but actual objects can.  This has some interesting repercussions in the .Net world when they made the jump from VB6 6 to VB.Net and the creation of C#.

In .Net MS was trying to intrude on Java's market.  In Java everything is an object which means every variable is really just a reference pointer to another object in C++ terms.  However, they couldn't change the rules on how things like Integer, Double, and String worked because that'd upset the legacy VB6 guys so they carved out exceptions for their primitives and let them behave like the did in VB6 which is just how they would behave in C or C++.  You can't set them to null because they're not references to object like they are in Java.  Now, a whole slew of programming in .Net is centered around databases these days so that created a little bit of an annoyance.  Eventually, can't remember when, the .Net world implemented 'generics', which are much like a C++ template,  after Java 1.5 was released with that feature and introduced the Nullable generic type.  Now you could declare a primitive to be Nullable and they'd play just like a Java primitive would.  Syntax looks like this in VB:

Dim intThing as Nullable(of Integer)

Or in C#:

Nullable<int> intThing;

And then the shorthand version in C#:
int? intThing;

That last one has amused me for years.  When I first ran across it I wondered how many emails would end in , "so do you know what's wrong?  I declare it as an int??"  Never seen that surface in real life though.

One last thing about the VB/.Net world:  Because the ADO and ADO.Net DB libraries were created before the Nullable type was introduced they had to create a special value for 'NULL' returns from a DB.  So we have the 'DBNull' value.  Not sure how that works under the hood but it's annoying as heck as a guy that went from doing Java for a few years back to .Net.  Something like IF(sqlReader("ColumnName") Is Nothing) won't fly.  You have to compare to DBNull if the column is actually in the underlying DataTable.  Irks me but that's part of the legacy crap that had to be carried over from VB6 to make it fly smoothly.

Eventually it all makes sense.  You'll just have to trust me on that.

[Headless Thompson Gunner]

DBNull is just dumb.  Dumb dumb dumb.   

Null is null.  Or should be, anyway.  There shouldn't be multiples kinds of it, or multiple names for it.

[CNYCacher]

DBNull is just dumb.  Dumb dumb dumb.   

Null is null.  Or should be, anyway.  There shouldn't be multiples kinds of it, or multiple names for it.

No, NULL is not NULL.  That's the whole point.

If you want to represent nothing, then use empty strings and 0 for numbers.  NULL means "no value"  An empty sting has a value, 0 is a value.  NULL has no value.

[tyme]

depends on the language.  and if NULL/nil/none cast to an integer is zero, then lots of other things may equal zero when cast as an integer.

Code: [Select]

irb(main):001:0> [ "a".to_i, nil.to_i, nil==nil, nil==0, nil===0 ]
=> [0, 0, true, false, false]
irb(main):002:0> RUBY_VERSION
=> "1.9.1"

Python 2.6.4 (r264:75706, Jan 28 2010, 22:54:14)
[GCC 4.4.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> (None==None, None==0)
(True, False)

# int(None) gives an error


[taurusowner]

[Tallpine]

I've done stuff like define a short (16 bit) to hold a byte value.

Then by my definition, -1 or 0xFFFF is null, undefined, error, etc.

Works good for a function that returns a byte in the range 0-255, but might need to return an error value instead.


And when I'm not herding bits and bytes, I'm riding my horse or cutting firewood or shooting or something