What is Cartesian product?
The implementation of cartesian products in SQL is not only Cross Join ). All join methods are converted into temporary Cartesian Product tables. Cartesian product is a concept in relational algebra, the number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set.
See the following example:
- DECLARE @Temp TABLE
- (GroupID INT ,
- GroupName VARCHAR(25),
- ItemNumber varchar(25)
- )
- INSERT INTO @Temp
- SELECT 1,'5805','27-196-018'
- UNION
- SELECT 1,'5805','27-196-019'
- UNION
- SELECT 2,'5805','27-196-020'
- UNION
- SELECT 2,'5805','27-196-021'
- UNION
- SELECT 3,'5805','27-196-022'
- UNION
- SELECT 3,'5805','27-196-023'
-
- SELECT
- G1_GroupID
- ,G1_ItemNumber
- ,G2_GroupID
- ,G2_ItemNumber
- FROM (
- SELECT
- GroupID AS G1_GroupID
- ,ItemNumber AS G1_ItemNumber
- FROM @Temp
- WHERE
- GroupID IN(1)
- ) AS A CROSS JOIN (
- SELECT
- GroupID AS G2_GroupID
- ,ItemNumber AS G2_ItemNumber
- FROM @Temp
- WHERE
- GroupID NOT IN(1)
- ) AS B
- ORDER BY A.G1_GroupID,A.G1_ItemNumber
- /*Result
- * 1 27-196-018 2 27-196-020
- * 1 27-196-018 2 27-196-021
- * 1 27-196-018 3 27-196-022
- * 1 27-196-018 3 27-196-023
- * 1 27-196-019 2 27-196-020
- * 1 27-196-019 2 27-196-021
- * 1 27-196-019 3 27-196-022
- * 1 27-196-019 3 27-196-023
- */
Recommended by editors]