Example to use CROSS JOIN or CARTESIAN JOIN in SQL SERVER

Introduction: In this article i am going to explain How to implement Cross Join or Cartesian Join in Sql Server using an example.

In previous articles i explained Use of SELF JOIN in SQL SERVER and Use FULL OUTER JOIN in SQL SERVER and What is cursor? Advantages and disadvantages,when to use and example to explain and Transaction in Sql server and Difference between Delete and Truncate

Description: A cross join that does not have a WHERE clause produces the Cartesian product of the tables participating in the join.  Result of Cross Join is like Cartesian product of the number of rows in the first table multiplied by the number of rows in the second table i.e. each row from the first table is combined with each row from the second table. 

For example, if table1 contains 5 records and table2 contains 3 records then result of the query will be 5*3 = 15 records.

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

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

Note:

• If a WHERE clause is added then cross join works similar to an inner join.
•  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]

Implementation: Let's understand the 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]

(

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

                [Name] [varchar](50) NULL,

                [Address] [varchar](200) NULL,

                [City] [varchar](50) NULL

)

--Insert Sample data

INSERT INTO EmDetails (Name,Address,City) VALUES ('Lalit','#1234','Chandigarh')

INSERT INTO EmDetails (Name,Address,City) VALUES ('Raman','#4567','Panchkula')

INSERT INTO EmDetails (Name,Address,City) VALUES ('Arjun','#111','Noida')

INSERT INTO EmDetails (Name,Address,City) VALUES ('Rohan','#321','Delhi')

INSERT INTO EmDetails (Name,Address,City) VALUES ('Raghav','#675','Noida')

It will look like as shown in image below:

• Create another table for storing the departments using the script below.

 CREATE TABLE [dbo].[Tb_Dept]

(

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

                [DEPT_NAME] [varchar](50)NULL

)

--Insert Sample data

INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('HR')

INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('IT')

INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('SALES')

It will look like as shown in image below:

A CROSS JOIN can be specified in two ways. Both will produce the same result set.

First using the CROSS JOIN syntax as:

SELECT * FROM EmDetails CROSS JOIN Tb_Dept

Second by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria as:

SELECT * FROM EmDetails , Tb_Dept

Output will be like as shown in image below:

Explaination

The above Cross Join query produced the combination of all the rows from the Employee table with every row of the Department table. Since the Employee table contains 5 rows and the Department table contains 3 rows, therefore it produced 5*3=15 rows. 

Now over to you:

" I hope you have got how to implement CROSS JOIN in SQL SERVER using the example above and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post