![]() It's best for everyone involved to always treat NULL as "unknown" rather than "empty", particularly for complex data types. This means that when writing expressions, you MUST be keenly aware of where NULL will pop up and take that into account. This "NULL poisoning" can easily cascade when you write complex expressions using many columns where the values can be NULL. One key exception is the IS NULL operator, which returns TRUE if its operand is NULL, FALSE otherwise. Generally speaking, any expression will return NULL if any of its operands is NULL. In most relational databases, NULL has many special properties that make it unsuitable for general use. That could be a good use of an empty string, but a more correct choice might be to just store their surname in that column as well. What about someone who is not married, or is married but didn't change their surname? Well, that is a KNOWN value, so it's NOT appropriate to use NULL there. Storing a NULL there should still mean "This Is Unknown". This would be used to track a person's pre-marriage surname, for married people who take their partner's surname. Getting to the meat of your question: strings. In the case of something like a Date of Birth, or a Height In Centimeters column, it makes perfect sense to store a NULL there if the value is not known. Generally speaking, a database NULL should be thought of as "This Is Unknown". The second, more complicated logic, is indeed what you have to do in Oracle, as NULL and '' are the same thing there. If you use NULL you have to do WHERE columnX= or (columnX is NULL and searchvalue is NULL). If you (in non Oracle) use '' you can just generate WHERE columnX = and it will work for equality searches. To get back to my Oracle experience: Say you want to (Both are not possible in Oracle, as '' = NULL there.)įrom my experience, '' makes a lot more sense when processing the data, as normally you would like to process the absence of a string as the empty string: Concatenation, Comparison, etc. I really can't think of any compelling example where differentiating between NULL and zero-length-string adds any value in the DBMS.įrom which follows: You either have a NULLable column that doesn't allow zero-len '' or a NOT NULL column that allows zero-len. Having had to work with Oracle ( which doesn't allow you to differentiate) I have come to the following conclusion:įrom a logical POV it doesn't matter. ![]() ![]() When you can distinguish between '' and NULL you are able to express that your setting is empty and avoid that the default applies. One of the exceptions I know, is when your column represents some setting and you have not empty defaults for them. Working with '' is generally easier and in most case there is no practical need to distinguish between both. ![]() Now looking at DBMS where '' is not identical to NULL (e.g. When you want to express explicitly, that a value is assigned, you have to use something like ' '.Īnd you have to worry whether trimming not empty results in NULL select case when ltrim(' ') is null then 'null' else 'not null' end from dual Other DBMS would return NULL in these cases. NULL varchars are treated as empty strings. On the other side, when concatenating strings in Oracle. Is syntactically correct, but it never returns a row. Using select * from mytable where varchar_col = '' The following leads to the same result Update mytable set varchar_col = NULL īut to select the columns where the value is empty or NULL, you have to use select * from mytable where varchar_col is NULL You set a varchar2 to an empty string like this: Update mytable set varchar_col = '' This clearly demonstrates that it is not necessary to distinguish between both. There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. I do not know about MySQL and PostgreSQL, but let me treat this a bit generally.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |