Use of Cross Join

Generally the cross join will used to get the possibilities among the two differnt set of data.

Cross Join will retun all the possible records between the two table.
Consider the each table has four rows then then result of corss join will return 4 x 4 = 16 Recrods.


create table Teams (id bigint identity(1,1), TeamName nvarchar(100))

insert into Teams
select 'India'
union all
select 'Australia'
union all
select 'Pakisthan'
union all
select 'Srilanka'

In the above table the number of team names are list. If we need to get the possibilities of matches between the teams for that kind of times we can go for cross joins.

select * from Teams A
cross join Teams B

This cross join result will give the 16 posibilities of matches between the four teams.

But the 16 possibilities inclues
Inida Vs India,
Australia Vs Australia
Srilanka Vs Srilanka
Pakisthan Vs Pakistan

For the first round of matches the above possibilities are not able to be a match. we need to avoid this combination of matches.

For this Problem

select * from Teams A
cross join Teams B
where !=

If we use the above query then the result will be fine.

The result will give the posibilities in the real time matches.


Author: Christopher F27 Jan 2010 Member Level: Gold   Points : 0

good posting,and need more explanation


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: