SQL Cross Join

Introduction

Learning different types of JOIN in any RDBMS is vital for any aspiring DBA or database developer. Understanding the concepts of JOIN, INNER, and OUTER is crucial as learning CROSS JOIN.

However, CROSS JOIN is sometimes ignored because people have the suggestion that it isn't used much in a real-world scenario. There could be some truth in that statement, but for any aspiring DBA or database developer, it is vital to know its concept.

In this article, we'll discuss the concept of SQL's CROSS JOIN and provide easy-to-understand examples.

Ok, let's get started.

What's CROSS JOIN?

Usually, CROSS JOIN is the last thing to be learned when learning the different types of JOINS.

Moreover, it is also separate from the other JOIN operations we're used to, such as INNER and OUTER JOIN.

But to answer, what is a CROSS JOIN? It is used to combine two separate sets that don't have any shared data.

Things to remember about CROSS JOIN:

• It combines each row from the first table with each from the second.
• All possible combinations of two different tables are the output.
• It doesn't establish a relationship between the joined tables.
• It returns a Cartesian Product of the joined tables.

What's Cartesian Product?

In the set theory concept, the Cartesian Product is the multiplication operation that produces all ordered pairs of the provided sets.

Let's assume that set A is a set of elements of x,y, and z. Then another set B with elements of 1,2,3.

The Cartesian Product of these two, A and B, is denoted as AxB, and the outcome follows.

AxB = (x,1), (x,2), (x,3) (y,1), (y,2), (y,3) (z,1), (z,2), (z,3).

Let's see an image analogy below.

Syntax

--SYNTAX
SELECT COL1, COL2, ... FROM [TABLE1], [TABLE2]
-- SYNTAX 
SELECT COL1, COL2, ... FROM [TABLE1] CROSS JOIN [TABLE2]

As you can see from the syntax guide above, you can use either of these two, and both are working as expected.

The main difference in the syntax compared to other JOINs such as INNER, LEFT (OUTER), RIGHT (OUTER), and FULL (OUTER) is that it doesn't require a joining condition.

Examples

In this section, we'll focus on the previous image analogy. We'll do it by creating the table structure, then putting some data on it and using the CROSS JOIN syntax.

Let's build the products table first.

DROP TABLE IF EXISTS #ProductsTemp
GO 
CREATE TABLE #ProductsTemp(
    Id int, 
    ProductName nvarchar(50)
)

INSERT INTO #ProductsTemp VALUES 
(1, 'Mouse'),
(2, 'Keyboard'),
(3, 'Mouse Pad')

SELECT * FROM #ProductsTemp

Output

Then, in this section, we'll build the employees' table.

DROP TABLE IF EXISTS #EmployeesTemp
GO 
CREATE TABLE #EmployeesTemp(
    Id int, 
    EmployeeName nvarchar(50)
)

INSERT INTO #EmployeesTemp VALUES 
(1, 'Jin Necesario'), 
(2, 'John Doe'), 
(3, 'Eric Doe')

SELECT * FROM #EmployeesTemp

Output

Now that we have the structure we need, let's use the CROSS JOIN; then, you try to compare the results from the previous image.

SELECT ProductName, EmployeeName FROM [#ProductsTemp], [#EmployeesTemp]
-- or
SELECT ProductName, EmployeeName FROM #ProductsTemp CROSS JOIN #EmployeesTemp

To get exactly, what we want based on the image analogy, see the code sample below.

SELECT ProductName, EmployeeName FROM #EmployeesTemp CROSS JOIN #ProductsTemp

Summary

In this article, we have discussed the following:

• What's CROSS JOIN?
• What's Cartesian Product?
• Syntax
• Examples

Once again, I hope you have enjoyed reading this article/tutorial as much as I have enjoyed writing it.

Stay tuned for more.

Until next time, happy programming! Please don't forget to bookmark, like, and comment.

Cheers! And Thank you!