3 ms·
Since I'm getting downvoted, I find nulls way more useful than outer joins, if you disagree I'd be interested in examples.
by patrec 7y ago
Since I'm getting downvoted, I find nulls way more useful than outer joins, if you disagree I'd be interested in examples.
- Tostino 7y agoThere are plenty of cases where the data model requires left joins (or some equivalent syntax using subqueries) to answer certain queries, and in any complex long lived application you will have queries you need you didn't anticipate when you originally modeled your data.
- patrec 7y agoExamples to base this discussion on would help. I'm not a hardcore DB person, but I've designed and modified schemas and queries for quite a few DBs over the years and not once can I recall feeling the need to do an outer join for production code, nor do I recall seeing a hardcore DB person using them (but that might just reflect limited exposure on my part). On the other hand, I do see LEFT OUTER JOINs a lot in code written by inexperienced people. Basically, the main use case I have for "dynamically creating" NULLs is quick inspection of data by shoe-horning something with more dimensions into the rows with equal numbers of columns model (CUBE and also OUTER JOIN) – but this is essentially just for display purposes. From a data modelling perspective outer joins seem quite dodgy to me and an aggregating subquery (such as in the teachers example) is not really conceptually equivalent, even if you can implement them with left joins, and in some DBs such as MySQL they have been traditionally faster. An aggregating subquery is a perfectly conceptually "clean" operation, no ternary logic involved.