Tuesday, 17 March 2015

Cross Join without Duplicate Combinations

How can u remove the Duplicate combinations by using cross join?

Generally Cross join produces the Cartesian product of the tables involved in the join operation. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

For Example : First table contains 3 rows , Second table contains 3 rows . By applies cross join operation then u get 9 rows Output.

But in this Article, I will explain how to get Combinations with out Duplications by using Cross Join.

Briefly explain as follows

Creating a Table in SQL Server

create table Teams
(id int identity (1,1),
TeamName varchar(10))

Inserting values in the Table

insert into Teams values('IND')
insert into Teams values('WI')
insert into Teams values('NZ')

The table looks like as follows

select * from Teams













To apply Crossjoin operation with Teams table

select A.TeamName,B.TeamName from Teams A cross join Teams B















Finally u got result to eliminate duplicate combination from the output of above query by using where clause.

select A.TeamName,B.TeamName from Teams A cross join Teams B
where A.TeamName>B.TeamName


No comments:

Post a Comment