Questions tagged [sql-null]

In ansi-sql, “NULL” stands for the “unknown” value.

In a boolean-expression, it has a special three-valued-logic, so that the following holds:

• NULL AND X is the same as NULL
• NULL OR X is the same as X
• NOT NULL is the same as NULL

Also, most sql-functions and operators, when operating on a NULL (unknown) argument, will produce a NULL result.

That holds for the equality-operator in particular, so the expression X = NULL will not produce TRUE or FALSE, but NULL.

Use x IS NULL to test for NULL-ness and X IS NOT DISTINCT FROM Y to test if X and Y are either both NULL or both not NULL and equal.

oracle database deviates from the SQL standard by treating empty strings as NULL, so don't rely on that if you want to write portable SQL.

Two guidelines for proper use of NULL:

• Define your database columns as NOT NULL (which is not the default) wherever possible.

  This improves the quality of your data and makes your queries simpler and consequently faster, since they don't have to deal with the oddities of NULL.

  It is trivial to change a column from non-nullable to nullable, but not vice versa!

• Use NULL for unknown values, not for values that are known to be absent, infinite values and the like. That will make your SQL intuitively do the right thing.

  For example, a missing comment had better be an empty string than a NULL, so that string operations work as expected.

  Infinite values are better represented by infinity (if your SQL dialect supports that) or values beyond the normal range, so that comparisons have the intended result.