7 ms·
As I wrote before: http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/ http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi... "NULL
by jeffdavis 7y ago
As I wrote before:
http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/ http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-wi...
"NULL semantics are designed to make you think you understand them, and think that the semantics are intuitive, and think that it’s part of some ingenious consistent system for managing missing information. But none of those things are true."
Any explanation you come up with does not generalize to other cases. NULL is just a bunch of special cases piled on top of each other, and really only kind of works because SQL is not designed to be very extensible (and to the extent it is, the standard doesn't give much guidance on how NULL should behave in these extensions).
- iaabtpbtpnn 7y agoSQL needs to have NULL, because otherwise how does an outer join work? And once NULL is there, you need rules for how it interacts with other values. What is the alternative?
- mjw_byrne 7y agoThere are in fact alternatives to NULL for outer joins. For example, the SAS data analysis system has an operation akin to a join which gives you an explicit variable saying whether a corresponding row in the joined table was found. In the result of the join, rows where no row was found get zero data values. Whether you consider that "good" or not is a matter of taste - personally I quite like it because it is very explicit and non-magical and I think NULL semantics cause more problems than they solve.
- ainar-g 7y agoSum types?
- pezezin 7y agoSum types is something that I really miss when working with SQL, they would make the data model much more powerful and consistent.
- jeffdavis 7y agoA left outer join duplicates the tuple on the LHS for each tuple on the RHS. It's almost exclusively used for grouping operations, so then you need to group the LHS tuples back into a single tuple and aggregate the RHS. But that's just weird -- in a normal programming language you would never duplicate the tuples and make this mess; you would keep a single tuple on the LHS and group the RHS into a set (or array/list). Then you'd aggregate the set on the RHS. That model is simple because an empty set is a value with known semantics (sum of an empty set is zero), whereas a half-NULL row is just a hack. Before someone asks, this has nothing to do with declarative/imperative, and nothing to do with some purity of the relational model. You can still have a declarative relational model with values that happen to be sets (or even relations).
- marcosdumay 7y agoAn outer join is also used to query optional values and alternatives. There are probably many other use cases I'm not remembering now. Some programming languages have a rich set of containers to replace what SQL does with outer joins, others simply lack them, and pile hacks over hacks that are always much worse than the SQL one.
- patrec 7y agoWhy would you need outer joins any more than nulls?
- pavel_lishin 7y agoShow me all schools in the system, and their associated teachers, including schools which have no teachers.
- patrec 7y agoEasy: select school, (select array_agg(teacher) from teachers where teachers.school = schools.school) from schools; If you don't like nulls any more than outer joins, you'd probably want: select school, (select coalesce(array_agg(teacher), '{}'::text[]) from teachers where teachers.school = schools.school) from schools;
- derefr 7y agoYou realize that joins are a part of the formal definition of relational algebra, while subqueries aren't, right? SQL has had outer joins since the beginning, but subqueries came only gradually, and more limited SQL engines tend still to not support them.
- patrec 7y agoWhy would SQL (fundementaly) need outer joins? "Because it has them", is not really a compelling answer in this context. A "pure" relational model (like the one C.J. Date has been going on about for decades) has no outer join.
- patrec 7y agoSince I'm getting downvoted, I find nulls way more useful than outer joins, if you disagree I'd be interested in examples.
- Tostino 7y ago
- int_19h 7y agoNULL is actually not a good way to represent an outer join, because an outer join should not produce any "unknown" values - only "missing" values, but NULL's semantics is closer to "unknown".
- derefr 7y agoIt depends on your data ingestion semantics. In a data-warehouse built up from event-sourced data, "missing" and "unknown" are the same thing: "I don't have this in here yet, so once I get it, it might turn out to be anything." For a column with a value you know is "nothing", I'd suggest you keep a separate column to denote the type of the column, just as you would in a union data structure. After all, the explicit choice to have or not have a value is, itself, data, that you can either be in possession of, or not. You can't use one sentinel value to indicate both "absence of value" and "absence of knowledge of absence-state of value"; you need two! (Of course, if your knowledge of the absence-state is absent, the data is probably absent too, so you'd probably have a CHECK constraint to assert that if the tag is NULL, the data must also be NULL.)
- Thaxll 7y agoHow do you handle an empty string vs a string that was never set?
- ibrar74 7y agoWhat do you mean by never set? In the database, you can either enter a string (empty or non-empty) or NULL in a column. There no such thing called never set.
- Thaxll 7y agoThat's my question, if you have a field that represents something and that field is a string that can be empty, null or contains anny string, if you don't use NULL how do you make the difference between an empty string and a null string ( user set "" vs never set anything )