Cross Join - Animated

Last modified: December 31, 2019

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

gif showing how cross joins work on tables

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.

CROSS JOIN: How many combinations of friends and connections do I have?
UNION: How many friends do my Facebook friends have and how many connections do my 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?
FULL OUTER join: How many friends and connections do my Facebook friends or LinkedIn connections have?

Written by: Matt David
Reviewed by:

Created by