In which situation cross join to be used in sql server

Posted by Jitendrasoft09 under Sql Server on 6/26/2013 | Points: 10 | Views : 899 | Status : [Member] [MVP] | Replies : 3
Hi All,
I want to understand In which situation cross join to be used in sql server, let me know if anybody knows.

Jitendra Kumar
If my post helps you, plz mark as an answer.



Responses

Posted by: Satyapriyanayak on: 6/26/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved
in the join. 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. The common example is when company wants to combine
each product with a pricing table to analyze each product at each price.

If this post helps you mark it as answer
Thanks

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 9/6/2013 [Member] Starter | Points: 25

Up
0
Down
cross join is an cartesion product matrix format. cross join where we user means i need repeation record more times means to go cross join..


select * from categories cross join products

cross join doesnot have where condition...


hope this will help....

sriram

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 9/6/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Jitendra Kumar,

1. The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN.
2. This kind of result is called as Cartesian Product.
3. If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
4. An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.
5. A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.
6. SQL CROSS JOIN syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]


OR

SELECT * FROM [TABLE 1], [TABLE 2]


7. EXAMPLE :

Let's try with 2 tables below:

Table 1: GameScores

PlayerName DepartmentId Scores
Jason 1 3000
Irene 1 1500
Jane 2 1000
David 2 2500
Paul 3 2000
James 3 2000

Table 2: Departments
DepartmentId DepartmentName
1 IT
2 Marketing
3 HR

SELECT* FROM GameScores CROSS JOIN Departments 



Result:
PlayerName DepartmentId Scores DepartmentId DepartmentName
Jason 1 3000 1 IT
Irene 1 1500 1 IT
Jane 2 1000 1 IT
David 2 2500 1 IT
Paul 3 2000 1 IT
James 3 2000 1 IT
Jason 1 3000 2 Marketing
Irene 1 1500 2 Marketing
Jane 2 1000 2 Marketing
David 2 2500 2 Marketing
Paul 3 2000 2 Marketing
James 3 3000 2 Marketing
Jason 1 3000 3 HR
Irene 1 1500 3 HR
Jane 2 1000 3 HR
David 2 2500 3 HR
Paul 3 2000 3 HR
James 3 3000 3 HR

Happy Coding.

If it helps you or directs U towards the solution, MARK IT AS ANSWER

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response