In that case, `select * from users where middle_name is null` gives us a set of users to prod for missing information next time we talk to them. `...where middle_name = ''` gives us a list of people without a middle name, should we care.
Edit:
A related aside is that Django's ORM's handling of NULL gives me freaking hives. From https://docs.djangoproject.com/en/5.1/ref/models/fields/#nul...:
> If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL.
I've been around numerous Django devs when they found out that other DBs and ORMs do not remotely consider NULL and empty string to be the same thing.
It never ceases to amaze me they got themselves into a position where they had to introduce a VARCHAR2 type.
[0] https://docs.oracle.com/en/database/oracle/oracle-database/1...
One well-established approach[1] to NULLs is that they represent the above “closed-world assumption” of exhaustiveness to encompass values we don’t know. For example, the same relation could also contain the tuple (Leslie Lamport, 1941-02-07, NULL) to represent that Lamport was born on 7 February 1941 and lives to the present day.
We could then try to have some sort of three-valued logic to propagate this notion of uncertainty: define NULL = x to be (neither true nor false but) NULL (“don’t know”), and then any Boolean operation involving NULL to also yield NULL; that’s more or less what SQL does. As far as I know, it’s possible to make this consistent, but it’ll always be weaker than necessary: a complete solution would instead assign a variable to each unknown value of this sort and recognize that those values are, at the very least, equal to themselves, but dealing with this is NP-complete, as it essentially amounts to implementing Prolog.
[1] http://www.esp.org/foundations/database-theory/holdings/codd...
s/represent the above/relax the above/
(Sorry!)
I'm not sure I understand this. Can you explain it with an example?
I'm thinking something like:
But then I'm stuck.