FULL OUTER JOIN还是UNION？ [英] FULL OUTER JOIN or UNION?

问题描述

困惑如何实现这可能与FULL OUTER或UNION。我想以这样的方式加入结果：

  Table1 Table2 
 ---------- ----- ----------------- 
 ID名称薪资ID水果
 --------------- ----------------- 
 1约翰1000 1苹果
 1亨利4000 1芒果
 1史密斯1000 1西红柿
  

结果应该是

  ResultTable 
 ------------------------ 
 ID名称薪水水果
 --------- -------------- 
 1约翰1000苹果
 1约翰1000芒果
 1约翰1000西红柿
 1亨利4000苹果
 1亨利4000芒果
 1亨利4000番茄
 1史密斯1000苹果
 1史密斯1000芒果
 1史密斯1000西红柿
  




解决方案

你需要一个笛卡儿产品加入或交叉加入..

  SELECT 
 * 
 FROM 
 table1，table2 
  

或

  SELECT 
 * 
 FROM 
 table1 CROSS JOIN table2 
  

（参考： http：// publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymstcrojo.htm ）

confused how to achieve this maybe with FULL OUTER or a UNION. I want to join results in such a way that

Table1                     Table2
---------------           ----------------- 
ID  Name Salary           ID    Fruits
---------------           ----------------- 
1   John   1000           1     Apples 
1   Henry  4000           1     Mangoes 
1   Smith  1000           1     Tomatoes

Result should be

ResultTable
       ------------------------
       ID Name  Salary  Fruits
       -----------------------   
       1  John  1000    Apples
       1  John  1000    Mangoes
       1  John  1000    Tomatoes
       1  Henry 4000    Apples
       1  Henry 4000    Mangoes
       1  Henry 4000    Tomatoes
       1  Smith 1000    Apples
       1  Smith 1000    Mangoes
       1  Smith 1000    Tomatoes 

解决方案

You need a cartesian product join or Cross Join ..

SELECT 
  *
FROM
  table1, table2

or

SELECT
  * 
FROM 
  table1 CROSS JOIN table2

(reference: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymstcrojo.htm)

这篇关于FULL OUTER JOIN还是UNION？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！