Resources » Code Snippets » SQL

Use of Cross Join


Posted Date: 14-May-2009  Last Updated:   Category: SQL    
Author: Member Level: Gold    Points: 10



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.

Example:

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 A.id != B.id

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

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


Did you like this resource? Share it with your friends and show your love!

Responses to "Use of Cross Join "
Author: Christopher F    27 Jan 2010Member Level: Gold   Points : 0

hi
good posting,and need more explanation

thanks



Feedbacks      

Post Comment:




  • 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:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    fugio
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India