Don Gilman, P.E. (8/25/2010)
It would also be helpful to expand a bit on string manipulations using NULL. While this nice article is restricted to the DB Engine, I recently had to dig to find out why SSIS was attempting to insert a null string into a varchar column set to disallow NULL. 😉Turns out that SSIS string concatenation in the Derived Column Data Task will set the column to NULL if any of the input columns is NULL. The work around is using the SSIS ISNULL(<column>,TRUE, FALSE) for each expression... with TRUE being set to an empty string.... and FALSE being the original column.
The reason for that is the same reason why NULL + 5 = NULL, because NULL is considered unknown in all operations, including string concatenation, if you concatenate an unknown string to a known one the result will logically be unknown or NULL.
He did cover that when he said "Also, any non-logical expressions involving NULLs have an unkown, or NULL, result."