FULL OUTER JOIN or UNION ?

Question

Hi, 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 

Answer 1

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)

Answer 2

use cross join if db2 has it

Answer 3

You might want to rethink your DB naming standards - naming columns as "ID" but then allowing duplicates isn't a great idea IMHO.

Because of the duplication, it isn't quite clear what is needed, but assuming that ID is a red herring, I think a CROSS JOIN is the correct approach in your scenario

select t1.ID, t1.Name, t1.Salary, t2.Fruit 
from Table1 t1, Table2 t2 

OR

select t1.ID, t1.Name, t1.Salary, t2.Fruit 
from Table1 t1 CROSS JOIN Table2 t2 

Answer 4

As the other answers have said, if you want all the rows in Table1 for all the rows in Table2, then a cross join (ie. a cartesian join) is the answer.

On the other hand, in the scenario above, an inner join on ID would also return the same resultset:

select t1.ID, t1.Name, t1.Salary, t2.Fruit 
from Table1 t1 join Table2 t2 on t1.id = t2.id

Then again, if this query relates to the same problem as some of your Crystal questions, you might find this resultset more useful:

ResultTable

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

which can be obtained with this query:

select ID, Name, Salary, '' Fruit from Table1 union all
select ID, '' Name, NULL Salary, Fruit from Table2

Answer 5

Use a CROSS JOIN:

SELECT T1.Name, T1.Salary, T2.Fruit 
  FROM Table1 AS T1
       CROSS JOIN Table2 AS T2;

Avoid using infixed notation:

SELECT T1.Name, T1.Salary, T2.Fruit 
  FROM Table1 AS T1, Table2 AS T2;

I'm not quite sure why but folk definitely don't like the infixed notation, especially when you add a WHERE clause. They will tell you that CROSS JOIN is SQL-92, which is indeed correct but then infixed notation is SQL-92 as well.

Oh, and some folk call the infixed notation without a WHERE clause a Cartesian Product, which is indeed correct but then a CROSS JOIN is a Cartesian Product as well.