On Fri, 3 Jun 2016 11:16:33 -0700, Michael Moore (michaeljmoore@gmail.com) wrote about "[SQL] NOT NULL CHECK (mycol !='') :good idea? bad idea?" (in <CACpWLjPX-_80aXcJFbk7wxZWKPTs2Fyeywe=6HmgorzV2U=n7A@mail.gmail.com>):
In Oracle, a NOT NULL constraint on a table column of VARCHAR in essence says: "You need to put at least 1 character for a value". There is no such thing as a zero-length string in Oracle, it's either NULL or it has some characters.
So Oracle is not compliant with ANSI standard SQL.
"Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls."
I'm guessing that Oracle when the way they did just for convenience. It's hard to imagine a use case where you would NOT want to treat NULL the same as you would a zero-length string. I would think that when a user writes the query: SELECT customer_number from TCUSTOMER where customer name is NULL; what they actually want is:
SELECT customer_number from TCUSTOMER where customer name is NULL or customer_name = '';
