how to join two tables without repetation or the cells from second table in postgresql using PLSQL

Question

When I try to join the below two table I am not able to get the output I want by the join. I tried using join but it didn't work let me know if its possible with plsql

Table 1:          
col1 col2          
1    a                    
1    b                 
1    c                    
2    a                    
2    b                    
3    a                    

table 2:

col1  col2

1     x 

1     y

2     x

2     y

3     x

3     y

The output must be:

col1 col2 col3

1      a    x

1      b    y

1      c    

2      a    x

2      b    y

3      a    x

3           y    

If use the join I am not able to get the same output as above. The output I am getting is

1  a x

1  a y

1  b x

1  b y

1  c x 

1  c y

2  a x

.....
.....

3  a x

3  a y

Answer 1

What you are searching is called a FULL OUTER JOIN. The result of this join contains elements from both input-tables, matching records get combined.

You can find more information here: https://stackoverflow.com/questions/4796872/full-outer-join-in-mysql

Answer 2

Using Window functions, specifically ROW_NUMBER() and partitioning by the Col1 in both tables, we can get a partitioned row_number that can be used as part of the join.

In other words, it seems to me that the order that the records are in is crucial for the join and result set you are desiring. Furthermore, using @Benvorth's suggestion of a FULL OUTER JOIN to achieve the NULLs in both direction.. I believe this might work:

SELECT
    COALESCE(t1.col1,t2.col1) as col1,
    t1.col2,
    t2.col2
FROM
    (SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 ASC) as col1_row_number FROM table1) t1
    FULL OUTER JOIN
    (SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 ASC) as col1_row_number FROM table2) t2 ON
        t1.col1 = t2.col1 AND
        t1.col1_row_number = t2.col1_row_number

That ROW_NUMBER() OVER (PARTITION BY col1, ORDER BY col2 ASC) bit will create row number for each record. The row_number will restart back at 1 for each new col1 value encountered. You can think of it like a RANK for each distinct Col1 value based on Col2's value. Table1's output from the subquery SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 ASC) as col1_row_number FROM table1 will look like:

Table 1:          
col1 col2 col1_row_number        
1    a     1               
1    b     2            
1    c     3               
2    a     1               
2    b     2               
3    a     1

So we do that with both tables, then we use that row number as part of the join along with col1.

A sqlfiddle showing this matching your desired result from the question