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:
- We can't do math on nulls: 1 + NULL = NULL.
- 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..
- NULL doesn't equal NULL. More specifically, null is neither equal to, nor is it not equal, to null.
- WHERE NULL = NULL won't return any results. Neither will WHERE NULL <> NULL.
- 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.
- Joining on null values won't work either.
- Related:
- Aggregate functions such as AVG() will ignore nulls. AVG(4,4,4,4,4,NULL) will evaluate to 4 (not 3.33).
- 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.
- 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.
- 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:
