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.
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.