re: SQL 201: All about SQL JOINs VIEW POST

re: My wild guess would be that’s because FULL JOIN is an evidence of a design flaw. If you find yourself doing FULL JOIN your database structure requi...

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:

name_lf company name_fl fav_food
Amjit, Anush Apple
Borges, Benita Boiron
Combs, Cathy CVS Cathy Combs Carrots
Daher, Darweesh Dell Darweesh Daher Doritos
Ellis, Ezra EDF Ezra Ellis Endives
Fulvia, Frances Firestone
Frances Fulvia Fries
Grace Gao Garlic
Helen Hopper Hummus

Out of a table that looks like this:

ssn name_lf ph em company
A1A1A1 Amjit, Anush 1111111 Apple
B2B2B2 Borges, Benita 2222222 Boiron
C3C3C3 Combs, Cathy 3333333 CVS
D4D4D4 Daher, Darweesh 4444444 Dell
E5E5E5 Ellis, Ezra 5555555 EDF
F6F6F6 Fulvia, Frances 6666666 Firestone

And a table that looks like this:

social name_fl phone email fav_food age
C3C3C3 Cathy Combs 3333333 Carrots 33
D4D4D4 Darweesh Daher 4444444 Doritos 44
E5E5E5 Ezra Ellis 5555555 Endives 55
FFF666 Frances Fulvia 6666666 Fries 66
G7G7G7 Grace Gao 7777777 Garlic 77
H8H8H8 Helen Hopper 8888888 Hummus 88

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.

code of conduct - report abuse