What are some tips about nulls in SQL you can share?
As a novice, this is what I have so far:
- 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 of cases such as WHERE Field1 <> ‘ABC’. If Field1 is null, that row won't get selected, even though it isn't ‘ABC’. Because null is neither equal to, nor is it not equal, to anything.
Joining on null values won't work either.
- 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 when comparing values. If we are attempting to get rid of nulls, 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?
Thanks.