What is cross join SQL and When to use cross join SQL?

In these articles, we learn what is a cross join SQL and how to use the SQL Server CROSS JOIN to join two or more unrelated tables,

The CROSS JOIN SQL produces the result set which is the number of rows in the first table, multiplied by the number of rows in the second table if no, WHERE clause is used along with CROSS JOIN. it is also known as the Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables.

therefore, if the WHERE clause is used with CROSS JOIN, such as it functions like an INNER JOIN.

therefore, an alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the names of the tables involved, after a FROM clause.

Syntax

There are two ways implementations of the CROSS JOIN statement:
First using the CROSS JOIN syntax.
Second, using the FROM clause without using a WHERE clause.

1. Using the CROSS JOIN syntax.

In this implementation, we specify the keyword CROSS JOIN in between the table names we want to join.

2. Using the FROM clause without using a WHERE clause

In this execution, we use the FROM keyword along with the table names, these names are separated by the commas.

Pictorial Presentation of Cross Join syntax

There are the following illustrates the syntax of SQL Server CROSS JOIN of two tables.

The CROSS JOIN joined each row from the first table (T1) with every row from the second table (T2), in another way, the cross join returns a Cartesian product of rows from both tables.

Unlike the LEFT JOIN or INNER JOIN, the cross join does not establish a relationship b/w the joined tables.
Suppose the T1 table contains three rows A, B, and C and the T2 table contains three rows 1, 2, and 3.

Therefore, the CROSS JOIN gets a row from the first table (T1) and then creates a new row for every row in the second table (T2). after that it does the same for the next row for in the first table (T1) and so on.

furthermore, in this illustration, as you see that the CROSS JOIN creates nine rows in total, in general we can understand that if the first table has n rows and the second table has m rows, then the CROSS JOIN will result in n*m rows.

Another CROSS JOIN SQL Examples

Let’s consider the two tables, first table name employee and second table for department as given below:

Therefore, Executing cross join SQL on these gives us the following result:

Output:

Emp_name	age	salary	Dept_name	id
Donald	34	35000	Account	9
Donald	34	35000	Management	10
Charles	28	30000	Account	9
Charles	28	30000	Management	10
Sam	25	28000	Account	9
sam	25	28000	Management	10

Output:

Emp_name	age	salary	Dept_name	id
Donald	34	35000	Account	9
Donald	34	35000	Management	10
Charles	28	30000	Account	9
Charles	28	30000	Management	10
Sam	25	28000	Account	9
sam	25	28000	Management	10

In Interview frequently Asked Question

Question 1: What is the Cartesian Product?
Answer 1: The Cartesian product is a multiplication operation, which is the set theory that generates all ordered pairs of the given sets. therefore, let’s suppose that A is a set, and elements are {A,B}, and B is a set, and elements are {1,2,3}. furthermore, the Cartesian product of these two A and B is denoted AxB and the results will be such as the following.
AxB = {(A,1), (A,2), (A,3), (B,1), (B,2), (B,3)}

let’s see another example, suppose that there are two sets A{X,Y,Z} and B{1,2,3} the cartesian product of AxB is the set of all ordered pairs
(X,1), (X,2), (X,3), (Y,1), (Y,2), (Y,3), (Z,1), (Z,2), (Z,3)
therefore, the given following picture illustrates the Cartesian product of A and B.

Similarly, in the SQL a Cartesian product of two tables A and B is a result set in which every or each row in the first table (A) is paired with each row in the second table (B). therefore, let’s suppose that the A table has n rows and B table has m rows, then the result of the cross join of the A and B tables have
n*m rows.