SQL CROSS JOIN

What is CROSS JOIN in SQL?

CROSS JOIN is useful when you need all combinations. It maps each row of the first table with all the rows from the second table.

CROSS JOIN is also called Cartesian Product – if the first table has X rows and the second table has Y rows, it will return X*Y rows.

For example, if you have 3 distinct shirts and 2 distinct jeans and you want all possible combinations they can be used in, CROSS JOIN can help.

Mapping each row from SHIRTS to JEANS gives us 6 (3*2) combinations.

• Blue Shirt & Black Jeans
• Blue Shirt & Blue Jeans
• Black Shirt & Black Jeans
• Black Shirt & Blue Jeans
• White Shirt & Black Jeans
• White Shirt & Blue Jeans

Syntax of CROSS JOIN in SQL

There are two ways to implement CROSS JOIN in SQL.

• CROSS JOIN clause

• FROM clause without WHERE clause

Example of CROSS JOIN in SQL

We are hosting a survey on our website and the marketing team decided to broadcast the word a week in advance while we are still drafting questions for the survey.

Users can register for the survey throughout the week. When the survey starts, we want to get the cartesian product of all registered users and the finalized questions and save them in a table where we can record the answers of each user against all the questions.

We will be working with three tables, Users Table to store the details of registered users. Questions Table to store the drafted questions for our survey and an Answers Table which will store the result of CROSS JOIN in SQL.

We need all combinations, so using CROSS JOIN in SQL here – we get 12 rows

Cartesian Product of ‘users’ and ‘questions’ –

Using SQL INSERT INTO SELECT Statement to get all combinations in the ‘answers’ table.

We see that each user is mapped against all four questions we created for our survey, now we can record the answer by a simple UPDATE API.