Full Outer Join - Animated

This is the third most common type of JOIN in SQL. 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.

SELECT *
FROM facebook
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.

• FULL OUTER join: How many friends and connections do my Facebook friends or LinkedIn connections have?
• LEFT join: How many friends and connections do my Facebook friends have? (Regardless of if they are on LinkedIn)
• RIGHT join: How many friends and connections do my LinkedIn connections have? (Regardless of if they are on facebook)
• INNER join: How many friends and connections do my friends who are on both on Facebook and LinkedIn have?

Written by: Matt David
Reviewed by: Tim Miller