left join table with no duplicate

I have two tables -

t1:

col_1        col_2        col_3
10001        apple        3
10001        orange       2
10001        grapes       5

t2:

col_1        col_2        col_3        col_4
10001        apple        3            123
10001        orange       2            345

What SQL query would join these two tables; giving me a result like this:

co开发者_如何学Cl_1        col_2        col_3        col_4
10001        apple        3            123
10001        orange       2            345
10001        grapes       5     

Join tables, then use COALESCE to get the first non null data column

SELECT COALESCE(t1.col_1,t2.col_1) col_1,
   COALESCE(t1.col_2,t2.col_2) col_2,
   COALESCE(t1.col_3,t2.col_3) col_3,
   COALESCE(t2.col_4,'') col_4,
FROM t1
FULL JOIN t2 ON t1 ON t1.col_1 = t2.col1 AND t1.col_2 = t2.col_2 AND t1.col_3 = t2.col_3

(SELECT col_1, col_2, col_3 
FROM t1)

UNION DISTINCT

(SELECT col_1, col_2, col_3 
FROM t2)

SELECT t1.col_1, t1.col_2, t1.col3, t2.col_4
FROM t1
LEFT OUTER JOIN t2 ON t1.col_1 = t2.col_1 AND t1.col_2 = t2.col_2 AND t1.col_3 = t2.col_3

SELECT t1.*, 
       col_4 
FROM   t1 
       LEFT JOIN t2 using(col_2); 

If col_1 and col_2 identify a unique entry change the last line to:

LEFT JOIN t2 using(col_1,col_2);

If col_1, col_2, col_3 identify a unique entry change the last line to:

LEFT JOIN t2 using(col_1,col_2,col_3);