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

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

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

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

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

Sum types?

Sum types is something that I really miss when working with SQL, they would make the data model much more powerful and consistent.

Why would you need outer joins any more than nulls?

Show me all schools in the system, and their associated teachers, including schools which have no teachers.

Easy:

   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;

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

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

Since I'm getting downvoted, I find nulls way more useful than outer joins, if you disagree I'd be interested in examples.

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

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