Tips about nulls in SQL

BB1234

What are some tips about nulls in SQL you can share? As GIS professionals, not SQL developers, it's easy to make mistakes when dealing with nulls in SQL.

For example:

1. We can't do math on nulls: 1 + NULL = NULL.
   1. Sometimes, people assume nulls will be automatically treated as zero. But that's not the case. We would need to explicitly convert nulls to something else (like zero) using COALESCE(), NVL(), etc..
2. NULL doesn't equal NULL. More specifically, null is neither equal to, nor is it not equal, to null.
   1. WHERE NULL = NULL won't return any results. Neither will WHERE NULL <> NULL. 
   2. Further, be careful for cases like WHERE A <> B. Let's say A is 1 and B is null. That row won't get selected, because null is neither equal to, nor is it not equal, to anything.
   3. Joining on null values won't work either.
   4. Related: 
      • IS NOT DISTINCT FROM
      • SQLite: Null-safe equals comparison using IS instead of =.
3. Aggregate functions such as AVG() will ignore nulls. AVG(4,4,4,4,4,NULL) will evaluate to 4 (not 3.33).
4. But be careful when doing math on the result of aggregate functions. Example: SUM(A) + SUM(B). Remember, we can't do math on nulls. So if the result of either of those aggregate functions is null, then the entire expression will evaluate to null.
5. Different databases concatenate nulls differently. For example, in Oracle, nulls will be ignored in this concatenation: 'FOO ' || NULL || 'BAR' …will evaluate to… FOO BAR. But in other databases, that would result in NULL.
6. Different databases treat empty strings differently. For example, in Oracle, SELECT '' FROM MY_TABLE will result in null. That's important to know for things like #1 above. We wouldn't want to replace NULL with '', since '' would simply result in null. With that said, putting a space ' ' between the single quotes would work; that won't get automatically converted to null.

Are there any other gotchas or tips about nulls you can share? Or any improvements or corrections to the points above?

 

Related:

• NULL in SQL: Indicating the Absence of Data
• Oracle Documentation - NULLS
• The Three-Valued Logic of SQL

BB1234

There are some good comments in this sister post:

Oracle Groundbreakers Community - Tips about nulls in SQL