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