Cross Join
-
Hi Experts,
Can any experts explain me the exact use of cross join and way of working ? Really i am unable to understand the concept of cross join .
Regards
Karthik
karthik
-
It created a cartesian product. It acted like a multiplication operation.
For example: TableA (ProductID INT), TableB (customer VARCHAR(10)
TableA
ProductID = 1, 2, 3
TableB
Customer = A, B, C
SELECT *
FROM TableA CROSS JOIN TableB
Result:
1, A
2, A
3, A
1, B
2, B
3, B
1, C
2, C
3, C
-
Simply put, each row from one table is returned with each row from the second or a Cartesian Product is the result set. So if each table has 10 rows, then the result of a cross join would bee 100 rows. Row one of table A would appear once with each row 1 through 10 of table B.
I commonly use this method when I need to get results for "every possible" combination of two result sets/tables.
See if this makes sense. Given a list of days/dates:
DECLARE @tableA TABLE (ndex INT IDENTITY (1,1), someDate VARCHAR(10))
INSERT @tableA
SELECT '5/1/2007' UNION
SELECT '5/2/2007' UNION
SELECT '5/3/2007' UNION
SELECT '5/4/2007' UNION
SELECT '5/5/2007'
and "parts" of the days:
DECLARE @tableB TABLE (ndex INT IDENTITY (1,1), someValue VARCHAR(10))
INSERT @tableB
SELECT 'Morning' UNION
SELECT 'Mid-day' UNION
SELECT 'Afternoon'
lets get every combination of the two:
SELECT *
FROM
@tableA a
CROSS JOIN @tableB b
ORDER BY
a.someDate, b.nDex DESC
The ordering is just there to read it easier.
ndex someDate ndex someValue
1 5/1/2007 3 Morning
1 5/1/2007 2 Mid-day
1 5/1/2007 1 Afternoon
2 5/2/2007 3 Morning
2 5/2/2007 2 Mid-day
2 5/2/2007 1 Afternoon
3 5/3/2007 3 Morning
3 5/3/2007 2 Mid-day
3 5/3/2007 1 Afternoon
4 5/4/2007 3 Morning
4 5/4/2007 2 Mid-day
4 5/4/2007 1 Afternoon
5 5/5/2007 3 Morning
5 5/5/2007 2 Mid-day
5 5/5/2007 1 Afternoon
Hope this clears it up a bit.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
answer of Karthik question is still not clear as I am also in the search of the utility (use) of cross join.
-
Where exactly cross join is applied in real time ?That is under which circumstance we need to apply cross join ?
karthik
-
Not many places. I've very very seldom needed a cross join. It's more common to have a cross join accidentily because of leaving out one or more join criteria.
Generally, if you need every possible combination of rows in 2 tables, then you'd use cross join. Jason had a good example in his post above.
Is not something you want to use often. The number of rows retuurned can get massive for even a small number of rows in the input tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
karthikeyan (12/3/2007)
Where exactly cross join is applied in real time ?That is under which circumstance we need to apply cross join ?I've had to use it most recently when we had a "project" that had a list of possible parts and possible criterion. this "project" was worked on over a period of time, lets say a month, but not every day of that month. I was required to supply a report that showed every possible combination of parts, criteria and days that could have occurred. A cross-join like the one above worked perfectly.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Here is the post that uses cross joins
--Ramesh
-
The main purpose of cross join is to give the all possible combinations of the join of the two tables
-
Cross joins - Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products. (A Cartesian join will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.)
-
Amazingly, SQL Query Processor performs a Cartesian product (a cross join, or an unrestricted join) between the first two tables that appear in the FROM clause, and as a result, virtual table is generated.
This virtual table contains one row for every possible combination of a row from the left table and a row from the right table. If the left table contains n rows and the right table contains m rows, it will contain n x m rows. Then, the logical expression in the ON filter (which is the first of three possible filters ON, WHERE, and HAVING that can be specified in a query) is applied to all rows in this virtual table.
--Ramesh
-
Ramesh (12/4/2007)
Amazingly, SQL Query Processor performs a Cartesian product (a cross join, or an unrestricted join) between the first two tables that appear in the FROM clause, and as a result, virtual table is generated.Why do you say that? What source or evidence do you have for that statement?
From everything I know of query processing, and from all exec plans that I've seen that is not the case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (12/4/2007)
Ramesh (12/4/2007)
Amazingly, SQL Query Processor performs a Cartesian product (a cross join, or an unrestricted join) between the first two tables that appear in the FROM clause, and as a result, virtual table is generated.Why do you say that? What source or evidence do you have for that statement?
From everything I know of query processing, and from all exec plans that I've seen that is not the case.
Gail,
Its true that, when you say something about anything you should have a proof for it. Thats what I follow.:cool:
I've recently read this in Inside Microsoft® SQL Server™ 2005 T-SQL Querying from Solid Quality Learning.
Here is the excerpt from it
http://safari.oreilly.com/0735623139/ch01lev1sec3
--Ramesh
-
Do you have chapter and page? I have the book at home. I'll check it out tonight.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Its there in Chapter 1 > Page 6
For those who didn't get their hands on the book...
Brief Description of Logical Query Processing Phases
1. FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.
2. ON: The ON filter is applied to VT1. Only rows for which the is TRUE are inserted to VT2.
3. OUTER (join): If an OUTER JOIN is specified (as opposed to a CROSS JOIN or an INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT2 as outer rows, generating VT3. If more than two tables appear in the FROM clause, steps 1 through 3 are applied repeatedly between the result of the last join and the next table in the FROM clause until all tables are processed.
4. WHERE: The WHERE filter is applied to VT3. Only rows for which the is TRUE are inserted to VT4.
5. GROUP BY: The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause. VT5 is generated.
6. CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5, generating VT6.
7. HAVING: The HAVING filter is applied to VT6. Only groups for which the is TRUE are inserted to VT7.
8. SELECT: The SELECT list is processed, generating VT8.
9. DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.
10. ORDER BY: The rows from VT9 are sorted according to the column list specified in the ORDER BY clause. A cursor is generated (VC10).
11. TOP: The specified number or percentage of rows is selected from the beginning of VC10. Table VT11 is generated and returned to the caller.
--Ramesh
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply