SQL query Help with OUTER JOIN ?

Question

Hi, I have two tables like this.

Table1

Column   |       Type       |
---------+------------------+
 cod     | text             |
 value99 | double precision |

Table2

Column   |       Type       |
---------+------------------+
 cod     | text             |
 value06 | double precision |

and i'd like to join them so i'd have something like

Column   |       Type       |
---------+------------------+
 cod     | text             |
 value99 | double precision |
 value06 | double precision |

the problem is that not all the codes are present in both tables, so if a code is not present in one of the tables it's value should be null.. In the end i'd like something like this

cod      |      value99     |      value06     |
---------+------------------+------------------+
 1       |     10           |       20         |
 2       |     13           |      NULL        |
 3       |     NULL         |        15        |

I think that its not possible using LEFT or RIGHT JOIN.. or maybe it is... any ideas? Thx=)

EDITED: I've tried the FULL OUTER JOIN but the result is something like

code    value  code    value
1       10     1    4
2    15     NULL    NULL
NULL NULL 3 36

ANSWER!!!: i found the answer thx to @Tobiasopdenbrouw :

SELECT test1.code,test1.value,test2.value FROM public.test1 LEFT OUTER JOIN public.test2 ON test1.code=test2.code
UNION
SELECT test2.code,test1.value,test2.value FROM public.test1 RIGHT OUTER JOIN public.test2 ON test1.code=test2.code

Answer 1

Use a FULL OUTER JOIN.

Using Full Outer Joins

To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

Consider a join of the Product table and the SalesOrderDetail table on their ProductID columns. The results show only the Products that have sales orders on them. The ISO FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

You can include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables. The following query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product (although all sales orders, in this case, are matched to a product).

Answer 2

I'm guessing a bit, because your question doesn't describe the required output in great detail, but what you probably need is a helper query that will create a table with all the codes for you (a UNION of 2 SELECT querys). This helper table can then be LEFT JOINED to your 2 source tables).

Edit: I thought of the (FULL) OUTER JOIN answer myself, but in reading between the lines, I don't think that's what the OP really needs. But I can be wrong, of course.

Answer 3

select 
    coalesce(t1.cod, t2.cod)
    ,t1.value99
    ,t2.value06
from 
    table1 t1 
    full outer join table2 t2 on t1.cod= t2.cod