6 ms·
SQL 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 th
by iaabtpbtpnn 7y ago
SQL 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.)