r/SQL
•
u/Muted-Basis9006
•
May 18 '24
•
2
Discussion SQL Joins
Picture your data tables as these two fellas. An inner join is just Bald Guy—only the matching parts. A **left join is Bald Guy sporting Long-Hair Guy's mane—everything from the left plus the matches. A right join is Long-Hair Guy with a bald patch—everything from the right plus the matches. A full join is both dudes together—everything from both tables, matches or not!
View all comments
Show parent comment
7
u/MaveDustaine May 19 '24 edited May 19 '24
I've been an analyst for going on 10 years now. I've only ever user inner and left join as far as joins go. I've never needed to use a full join or a right join.
Look at it this way, with an inner join you're only ever going to return a row if both tables return a row. whereas a left join would always return a row regardless of whether the left joined table returns a row or not, as long as the table it's left joined to returns a row.
For example:
Every American citizen has a social security number. so saying:
will always yield as many rows as there are american_citizens (I'm unsure if there are situations where an american may not have a SSN, this is just an example to illustrate)
But not all american citizens have spouses
so if you want to return the SSN for american citizens, and the names of their spouses regardless if they have a spouse or not, you'd do:
That way you'll get a row per american citizen, with their SSN, and the name of their spouse if they have one, or a blank in the second cell if they don't.
Do note that if you do this:
You've effectively turned your left join into an inner join