Cross Join in SQL

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

Overview

The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.

Scope

In this article, we will discuss

  • What are cross joins? with example.
  • Syntax of SQL CROSS JOIN.

Introduction to SQL Cross Join

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.

example of cross join
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 SQL Cross Join

There are two ways to implement CROSS JOIN in SQL.

  • CROSS JOIN clause

SELECT [COLUMNS] FROM [TABLE_1] CROSS JOIN [TABLE_2]
SELECT [COLUMNS] FROM [TABLE_1],[TABLE_2]

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

UsersQuestions
idnameemailidquestion
1Tomtom@gmail.com1Do you shop online?
2Dickdick@gmail.com2Do you rate products bought online?
3Harryharry@gmail.com3Do you prefer COD?
4Do you own a Credit Card?

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

SELECT * FROM users, questions;
--OR
SELECT * FROM users CROSS JOIN questions;

Cartesian Product of ‘users’ and ‘questions’ –

idnameemailidquestion
1Tomtom@gmail.com1Do you shop online?
2Dickdick@gmail.com1Do you shop online?
3Harry1Do you shop online?
1Tomtom@gmail.com2Do you rate products bought online?
2Dickdick@gmail.com2Do you rate products bought online?
3Harryharry@gmail.com2Do you rate products bought online?
1Tomtom@gmail.com3Do you prefer COD?
2Dickdick@gmail.com3Do you prefer COD?
3Harryharry@gmail.com4Do you prefer COD?
1Tomtom@gmail.com4Do you own a Credit Card?
2Dickdick@gmail.com4Do you own a Credit Card?
3HarryDo you own a Credit Card?

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

INSERT INTO answers 
(user_id, question_id)
(SELECT u.id AS user_id, q.id AS question_id FROM users u, questions q);

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.

user_idquestion_idanswer
11Yes
12Yes
13Yes
14Yes
21Yes
22Yes
23No
24No
31Yes
32No
33Yes
34No

Conclusion

As we see, CROSS JOIN in SQL is extremely powerful in cases where we want to work with all combinations or cartesian product of tables.

If we have three tables with all possible values for hours (0 to 23), minutes (0 to 59) and seconds (0 to 59) and if we cross join among them, we will get 86400 rows. One row for each second in a day. Such implementations make it convenient to generate test data.

Most of the time you will want to use powerful restrictive features of SQL and avoid pulling unnecessary rows while retrieving data from tables. Hopefully this article will redirect you to the right direction if you find yourself attempting any work with Cartesian products of tables.

Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors
rcbGet a Free personalized Career Roadmap from