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?
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.