I have a use case for FULL JOIN.
FULL JOINs are fantastic when, for one reason or another (perhaps you built the database just as a way to combine some external data sources ad-hoc), your database has acquired data in two different tables that includes different fact tables about the same real-world entities.
See my example in my recent blog post "Every SQL Join You'll Ever Need".
You'll see that it's easy to make a table that looks like this:
Out of a table that looks like this:
And a table that looks like this:
It may not be a terribly common business need in a well-normalized long-term data store (or, as you say, one that doesn't "require immediate surgery"), but it's a really common one in ETL / "set up the latest database management wants" work. :-)
I could certainly also see myself using a FULL JOIN in the "stations and sites" types of "what sort of data I'm dealing with" situations Dian described. Other people have a point that often times when a "business need" requires two such tables to be joined so often, one would think they might have attributes of their relationships that need to be stored and therefore need a junction table, so that's worth thinking about as one designs their database! But as Dian points out ... not necessarily.
I'd give that it definitely does seem to be far more common, business-question-wise, to want to see the intersection of two data sets or to see "definitely thing A, but only things B/C/D/etc. if applicable", than it is to want to see "whatever -- but put it together on the same line if it seems related"! :-) But I wouldn't call having a business need to look for such joins a "flaw" by any stretch of the imagination.
We’re a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.