>For example, Oracle database won’t allow you to have an empty string. Anytime Oracle database sees an empty string, it automatically converts the empty string into a NULL value.<p>Damn. This is how you do enterprise.<p>I might be the only person who likes SQL nulls. If you learn how they work up front, they're useful and not really that confusing. But if I ran into weird behaviors like this, I might hate them too.
Null values and inequality are extremely counterintuitive (in postgres at least). If you run the query:<p><pre><code> SELECT * FROM my_table WHERE my_column != 5
</code></pre>
You would expect it to return rows that have a null value for my_column, since null is not 5. However that is not the case.
Not sure what big deal is. You learn somewhere along the way that you check for null values with “is” vs “=“. Done, write it on a sticky note if you need, and move on.<p>“Why isn’t it consistent??” - well a lot of systems have a lot of bat shit crazy inconsistencies, some times there for good reason. You learn to keep them straight and get your shit done.<p>If you want to learn the “why” every time you encounter a system design quirk, be my guest but you may be going down a time intensive rabbit hole with little pay off for yourself.
Null values are so important in representing data. But they cause so much confusion in a) unexpected behaviors in queries and b) inconsistent handling across various engines... I sometimes wish <whisper> that they hadn't been included in the spec at all </whisper>. But then I come to my senses again, and go fix yet another bugged query for an analyst who didn't account for nulls in the data.
A pet peeve of mine is concatenating null strings. It's like a poison pill that nulls the whole result. 99.99% of the time that's NOT what one wants domain-wise. Maybe the standard should make another concatenation operator that treats null strings as zero length strings. Sure, one can de-null each string in the expression, but that's ugly anti-DRY code. Please fix it, I haaate that.
Now here's a blast from the past! 20 years ago this was common knowledge. Now it's making headline news on HN. SQL is back with the vengeance!
We recently moved from Oracle to Postgres. We had thousands of queries written based on the way Oracle handles NULLs and empty strings. It took us the better part of a year to rewrite all of them to the Postgres way. I am so glad to be off of Oracle.
I miss my past Oracle career, I've diagnosed this "= NULL" rather than "IS NULL" in so many broken queries, slow queries because of the way Oracle indexing handles NULL.<p>There is a lot of discussion in this thread about whether this implementation of null checking in Oracle is appropriate, analysing it, but the current implementation is just fine, it has been tested by time.<p>The internet does tend to rehash the same arguments over and over!!! The internet forgets. I remember these arguments 20 years ago.
Another one is MIN and MAX ignore NULL values, which make for some interesting rollback scenarios.<p>I also swear I have seen a gotcha involving UPDATE WHERE IN and not throwing an error where it should have, which is why I always quadruple check my update statements, but I wasn't able to reproduce it and couldn't find any information online. I haven't seen the issue in so long I forgot what it was, but it would update all rows in your table even if your WHERE clause was proper.
Theoretically NULL means "unknown" value. As it happens, most business applications do not have any requirement to deal with "unknown" values. These applications are only interested in acting on requests where all the required data are provided by the person responsible for entering the data. For example, when I transfer money from one bank account to another, the amount of the transfer can't be "unknown", the sending account can't be "unknown", and the receiving account can't be "unknown".<p>These same applications do have requirements to deal with empty values. Sometimes an empty value means "I haven't yet entered this value in the to the UI". But in that case the UI won't let you submit the form until you have supplied a valid value.<p>In other cases an empty value is a valid value. For example, "who is your spouse?" and the answer is "I'm not married".<p>Sometimes NULL represents "irrelevant", like for "who is your spouse?", where some of the records in the table represent people who can have spouses, and some of the records represent other person-like entities that aren't actually people and therefore they can't have spouses.<p>Given that NULL is _not_ being used to represent "unknown" values, and there is a requirement to represent empty values, and you don't want to have a whole extra column just to represent "emptiness", the most straightforward way to implement empty values is to use NULL. So that is what happens.<p>And you have to remember to use "is" instead of "=" when you want to test your empty NULL values for equality with other empty NULL values - because your SQL database is pretending that NULL really means "unknown", and it doesn't want to say that one unknown value is equal to another unknown value, because that would be theoretically incorrect.
there is something "missing". The SQL spec specifies `null = null` to be "unknown", where i sometimes expect "true". For MSSQL this can be configured using `SET ANSI_NULLS { ON | OFF }`. AFAIK MySQL can't be configured. Don't know about Postgres.
NULLs in subselects do bite me with distressing regularity: Writing<p><pre><code> SELECT ... FROM ... WHERE blah NOT IN (SELECT foo FROM bar);
</code></pre>
getting no hits until I slap my forehead and add WHERE foo IS NOT NULL to the subselect.
DynamoDB, which is NoSql, also doesn't accept empty strings.
But at least, Oracle automatically converts the empty string into NULL, comparing with DynamoDB which would actually fail the query.
With some columnar databases NULLs are 'free' because they are a default, absent state or compressed away. Can be another reason to prefer them with very large datasets.
In MySQL, NULL values are useful when using CONCAT_WS (concatenation with separator) or GROUP_CONCAT because NULL values will be ignored - so you don’t get e.g., “one,,two”.
I was dealing with NULLs whole day on MySQL workbench. It wasn't considering int as NULL value. Needed to make all empty cells 0 to be able to import data properly.