Cartesian product of SQL

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]