Full Outer Join
This is the third most common type of JOIN in SQL (might be cool to insert % from Chartio Users). FULL OUTER refers to the first table, or the table you will be joining to. So in this case it would be the facebook table since it comes before linkedin table in the query.
FULL OUTER JOIN linkedin
ON facebook.name = linkedin.name
This query finds matches and adds them to a newly created table in the same way as a LEFT join.
However after completing the LEFT join of the data then there is essentially RIGHT join performed. However since all the cases where there is a match has been made, SQL only checks to see if each value is present in the joined table. If it is not in the joined table, SQL will add this row to the new table and put in nulls for the columns from the other table.
Why use a FULL OUTER JOIN vs LEFT JOIN or RIGHT JOIN vs an INNER JOIN? To help understand, Let’s think about the different questions they are asking.
- How many friends and connections do my friends who are on both on Facebook and LinkedIn have?
- How many friends and connections do my Facebook friends have? (Regardless of if they are on LinkedIn)
- How many friends and connections do my LinkedIn connections have? (Regardless of if they are on facebook)
FULL OUTER join
- How many friends and connections do my Facebook friends or LinkedIn connections have?