SQL Cartesian Product

/*  ========================================================
Description:
Use SQL to implement Cartesian Product
Author: CC
Date: 2011.11.03
========================================================  */ 

 Use Test
 Go 

 Select   *   From L; --  Such as l  
  Select   *  From M; --  Such as m  
 
 --  Cross-join implementation, lm  
  Select   *   From L Cross   Join M;

L table

Lid name
-------------------------------
101 Beijing
102 Taiyuan
101 null
102 null

(Four rows affected)

 

M Table
Matid qty lid mname
-----------------------------------------------------
10011 1 101 Beijing
20012 1 102 Taiyuan
10011 1 102 Taiyuan
10011 1 102 Taiyuan

 

LM:

Lid name matid qty lid mname
------------------------------------------------------------------------------------
101 Beijing 10011 1 101 Beijing
102 Taiyuan 10011 1 101 Beijing
101 null 10011 1 101 Beijing
102 null 10011 1 101 Beijing
101 Beijing 20012 1 102 Taiyuan
102 Taiyuan 20012 1 102 Taiyuan
101 null 20012 1 102 Taiyuan
102 null 20012 1 102 Taiyuan
101 Beijing 10011 1 102 Taiyuan
102 Taiyuan 10011 1 102 Taiyuan
101 null 10011 1 102 Taiyuan
102 null 10011 1 102 Taiyuan
101 Beijing 10011 1 102 Taiyuan
102 Taiyuan 10011 1 102 Taiyuan
101 null 10011 1 102 Taiyuan
102 null 10011 1 102 Taiyuan

(16 rows are affected)