What is the use of Cross join in sql server ?

Posted by Kasani007 under Sql Server on 8/6/2014 | Points: 10 | Views : 579 | Status : [Member] | Replies : 5
What is the use of Cross join in sql server ?




Responses

Posted by: kgovindarao523-21772 on: 8/6/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,
Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows. There is no relationship established between the two tables – you literally just produce every possible combination.

The danger here, of course, is that if you have table A with 10,000 rows and Table B with 30,000 rows, and you accidentally create the product of these two tables, you will end up with a 300,000,000 row result -- probably not a good idea. (Though it is great for creating test data and the like.)

So, how can this ever be useful? Actually, if you do lots of report writing in SQL, a CROSS JOIN can be your best friend.

Thank you,
Govind

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

Posted by: Bandi on: 8/8/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

SQL CROSS JOIN syntax:

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

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




Important Points:
1. Sometimes, we need to generate millions of records for sampling data in the dev/testing environment. In those scenerios, you join two sets of data with CROSS JOIN to generate thousands/millions of records
2. 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.
3. The CROSS JOIN technique can apply to many situations – to return total labor cost by office by month, even if month X has no labor cost, you can do a cross join of Offices with a table of all months. Another classic example is showing all GL transactions for a specific set of companies and accounts, returning all accounts and companies even when they have no activity.
4. The important thing is to practice with very small sets of sample data until you get a feel for how it works. Also, you should explicitly state CROSS JOIN in your SELECT so that it is very clear that you intend for this to happen and it is not the result of missing joins.


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Manimaddu on: 8/11/2014 [Member] Starter | Points: 25

Up
0
Down
CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

Thanks & Regards,
Mani Kumar

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

Posted by: Bandi on: 8/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark as Answer if got clear idea/solution...

Post us back the issue/doubts if not resolved the issue

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

Using Cross Join we can get the result with multiple rows in both the tables.

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

Login to post response