Common SQL Join Types – Full Outer Join

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.

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.

 

INNER join

  • How many friends and connections do my friends who are on both on Facebook and LinkedIn 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)

FULL OUTER join

  • How many friends and connections do my Facebook friends or LinkedIn connections have?
Matt David

About Matt David

Hi! I'm Matt David. I have over 7 years industry experience using data and currently work at Udacity as Product Lead for the School of Data Science. Data has become a prerequisite skill set for more and more non-data jobs and I am passionate about making data concepts more easily understood to increase data literacy for everybody.