Online College Courses for Credit

+
Cross Joins

Cross Joins

Rating:
(0)
Author: Sophia Tutorial
Description:

Explain the what a result set from a CROSS JOIN represents.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using Cross Join to join data in tables.

A cross join is a unique type of join where we want to create a combination of every row from two different tables. Since all row combinations are included as part of the result, this is a reason why it is also called a cross product or cartesian join. One example in which we could use a cross join is if we wanted to get all of the potential combinations of certain products like all of the colors and sizes.

It is important to note that these cases are rare and most scenarios where we have a cartesian join between two tables means that we have a missing join statement between a common attribute.

The structure of the cross join looks like the following:

SELECT <columnlist>
FROM <table1>
CROSS JOIN <table2>;

Notice that in this case, there are no details of how they are joined. This statement would result in the same result set as if we did:

SELECT <columnlist>
FROM <table1>,<table2>;


Or we could even use an inner join with a condition that always evaluates to true to force a cross join:

SELECT <columnlist>
FROM <table1>
INNER JOIN <table2> ON true;

Let us give this a try by creating a couple of tables for the size and color and joining them together.

CREATE TABLE color ( color_id INT PRIMARY KEY, color_name VARCHAR (50) NOT NULL );

CREATE TABLE size ( size_id INT PRIMARY KEY, size_name VARCHAR (30) NOT NULL );

INSERT INTO color (color_id, color_name)
VALUES (1, 'Blue'), (2, 'Red'), (3, 'Yellow');

INSERT INTO size (size_id, size_name)
VALUES (1, 'Small'), (2, 'Medium'), (3, 'Large');

Now that the tables have been created, we should expect to see 9 rows being returned from the cross join as there are 3 rows in the color table and 3 in the size table:

SELECT color_id, color_name, size_id, size_name
FROM color
CROSS JOIN size;

File:11423-2170-1.png

Running each of the other queries results in the same results:

SELECT color_id, color_name, size_id, size_name
FROM color, size;

SELECT color_id, color_name, size_id, size_name
FROM color
INNER JOIN size ON true;

File:11424-2170-2.png

As you see in the results, it has taken each combination of the color and matched to each combination of the size. This type of combination can be quite problematic as well as the size of the data gets larger. In our example, we have 3 rows in the color table and 3 rows in the size table. Imagine if we had 1000 rows in one table and 1000 in another table. The result would have 1,000,000 rows in the result.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The CROSS JOIN clause makes a cartesian product of rows in two or more tables.