Common SQL Join Types – Cross Join

This is the fifth most common type of JOIN in SQL. Cross join does not look for matches between any values in the two data sets. Instead for each row in first table every row of second table will be attached to it and added to the final table one by one.

SELECT * FROM facebook CROSS JOIN linkedin

Why use a CROSS JOIN vs a UNION, LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL OUTER 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?

UNION

How many friends do my Facebook friends have and how many connections do my LinkedIn connections have?

CROSS JOIN

How many combinations of friends and connections do I have?

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.