Cartesian product of SQL

Source: Internet
Author: User

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:

 
 
  1. DECLARE @Temp TABLE 
  2. (GroupID INT ,   
  3. GroupName VARCHAR(25),  
  4. ItemNumber varchar(25)  
  5. )  
  6. INSERT INTO @Temp 
  7. SELECT 1,'5805','27-196-018' 
  8. UNION 
  9. SELECT 1,'5805','27-196-019' 
  10. UNION 
  11. SELECT 2,'5805','27-196-020' 
  12. UNION 
  13. SELECT 2,'5805','27-196-021' 
  14. UNION 
  15. SELECT 3,'5805','27-196-022' 
  16. UNION 
  17. SELECT 3,'5805','27-196-023' 
  18.  
  19. SELECT   
  20.     G1_GroupID   
  21.    ,G1_ItemNumber   
  22.    ,G2_GroupID   
  23.    ,G2_ItemNumber    
  24. FROM (   
  25.         SELECT   
  26.             GroupID AS G1_GroupID   
  27.            ,ItemNumber AS G1_ItemNumber   
  28.         FROM @Temp    
  29.         WHERE   
  30.             GroupID   IN(1)   
  31.      ) AS A CROSS JOIN  (   
  32.         SELECT   
  33.             GroupID AS G2_GroupID   
  34.            ,ItemNumber AS G2_ItemNumber   
  35.         FROM @Temp     
  36.         WHERE   
  37.             GroupID NOT IN(1)   
  38.      ) AS B   
  39. ORDER BY A.G1_GroupID,A.G1_ItemNumber  
  40. /*Result  
  41.  *    1    27-196-018    2    27-196-020  
  42.  *    1    27-196-018    2    27-196-021  
  43.  *    1    27-196-018    3    27-196-022  
  44.  *    1    27-196-018    3    27-196-023  
  45.  *    1    27-196-019    2    27-196-020  
  46.  *    1    27-196-019    2    27-196-021  
  47.  *    1    27-196-019    3    27-196-022  
  48.  *    1    27-196-019    3    27-196-023  
  49.  */  

Recommended by editors]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.