Archives of the TeradataForum

Message Posted: Fri, 19 Sep 2002 @ 22:19:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Re updates your assumption will be correct. It will also be correct for inner joins and a whole bunch of other things.

If you remember the following simple rules of thumb you should be OK:

* Null doesn't equal anything - including itself

* Null doesn't not equal anything - including itself

In other words NULL does not equal NULL; nor does it "not equal" NULL.

For example, all of the following expressions return false if c1, c2 or both c1 and c2 are null

* c1 = c2    -> False (even if both are NULL)
* c1 <> c2   -> False (even if both are NULL)
* c1 < c2    -> False
* c1 >= c2   -> False

I'm sure you get the picture.

The above applies to joins as well. So NULL's won't "match up" in subqueries, inner joins, or just about any other expression.

Finally, NULL's are pretty "destructive". If a NULL value finds its way into an expression, the result of the expression will pretty much be "toast". NULL added to, multiplied by, concatenated to any other value will result in a NULL value being returned. For example all of the following result in NULL if c1, c2 or both are NULL:

* c1 + c2
* c1 * c2
* c1 || c2
* substring (c1 from 1 for 4)
* substring ('a test message' from 1 for c1)

Etc etc etc.

Life is not all bad, as with most destructive things, NULLs can be quite useful, if used properly. If you haven't already done so, you might want to have a look at the coalesce, nullif, zeroifnull and nullifzero functions in the SQL reference manuals (Volume 5). You will find them in Chapter 1 - Case operations (as opposed to case expressions or case statements - OK I am showing my procedural language background here please forgive me) and chapter 2 - Arithmetic operators

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>