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.

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:

cross join or cartesian join example in sql server


  • 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:

cross join or cartesian join example in sql server


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:

cross join or cartesian join example in sql server


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."
Previous
Next Post »

7 comments

Click here for comments
January 12, 2014 ×

Your welcome Nabil Mosali..stay connected and keep reading for more useful updates like this ..:)

Reply
avatar
Anonymous
admin
January 18, 2014 ×

sir please post on WPF technology

Reply
avatar
January 19, 2014 ×

Hello, i will create articles related to WPF and publish them very soon..so stay connected and keep reading..:)

Reply
avatar
Unknown
admin
July 21, 2014 ×

Hello Sir I want to Know magic table in Sql SERVER

Reply
avatar
Anonymous
admin
August 01, 2014 ×

THANK U SIR

Reply
avatar
August 05, 2014 ×

your welcome..i am glad you found this article useful..stay connected and keep reading for more useful updates...:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..