CROSS JOIN or CARTESIAN JOIN in SQL SERVER

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows.

A cross join returns all possible combinations of all rows from both the tables. So basically it is like joining everything to everything.

use:

Cross Join will be particularly useful when we need to select all the possible combinations of rows and columns from both the tables. But this type of join is generally not preferred as it takes lot of time in generating all combinations and produces a huge result set that is not often useful.

Key points To Remember:

1.       If a WHERE clause is added then cross join works similar to an inner join.

2.        Cross join is a Cartesian join where the condition is optional to join

 Syntax for CROSS JOIN:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

                                 OR

SELECT * FROM [TABLE 1], [TABLE 2]

working of CROSS JOIN using an example.

 First of all create a table for storing Employee details like Name, Address and City using the Script below.

 CREATE TABLE [dbo].[EmDetails]

(

                [Emp_Id_Pk] [int] IDENTITY(1,1) NOT NULL,

                [Emp_Name] [varchar](50) NULL,

                [Salary] [varchar](200) NULL,

                [Dept_id] int

)

Insert some data in this table. It will look like as shown in image below:

Create another table “Dept_table” for storing the departments using the script below.

 CREATE TABLE [dbo].[ Dept_table]

(

                [DEPT_ID] [bigint] IDENTITY(1,1) NOT NULL,

                [DEPT_NAME] [varchar](50)NULL

)

Insert some data in this table. It will look like as shown in image below:

First using the CROSS JOIN syntax as:

SELECT * FROM EmDetails CROSS JOIN Tb_Dept

or

SELECT * FROM EmDetails , Tb_Dept

Output will be like as shown in image below:

The above Cross Join query produced the combination of all the rows from the Employee table with every row of the Department table.