CODEWITHSUNDEEP
sqljoinoracle11g
Rajnish Kumar
Rajnish Kumar

Reputation: 1

Joining two Oracle tables to fetch all rows in a column

I have two Oracle tables product1 and product2(below), Can someone please suggest on how to join them to get the output as Results.

Product1

product value
Apple 10
Banana 20
Grapes 30

Product2

product value
Orange 30
Apple 40
Grapes 30

Results

product value_t1 value _t2 diff
Apple 10 40 30
Banana 20 (-20)
Grapes 30 30 0
Orange 30 30

Upvotes: 0

Views: 573

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Use a full join to get all the produces in both tables and then coalesce() to treat the non-NULL values as 0:

select product, p1.value as value_1, p2.value a value_2,
       coalesce(p1.value, 0) - coalesce(p2.value, 0) as diff
from product1 p1 full join
     product2 p2
     using (product);

Upvotes: 1

Daud Mabena
Daud Mabena

Reputation: 23

In MySql Dialet this will work, Little twist might work for Oracle DB:

select
       IF(p1.product IS NULL, p2.product, p2.product) AS product,
       p1.value AS value_t1,
       p2.value AS value_t2,
       IF(p1.value > p2.value, p1.value - p2.value, p2.value - p1.value) AS diff
from product1 p1
LEFT JOIN product2 p2 on p1.product = p2.product;

Upvotes: 0

coding monster
coding monster

Reputation: 394

Try this.

SELECT
    CASE 
        WHEN T.product IS NULL THEN
            T.product
        ELSE
            T1.product
    END product
    , T.value value_t1
    , T1.value value_t2
    , CASE 
        WHEN T2.value IS NULL THEN
            0 - T.value
        WHEN T1.value IS NULL THEN
            T1.value
        ELSE 
            T1.value - T.value
    END diff
FROM product1 T
    OUTER JOIN product2 T1 ON T.product = T1.product

You have to sure default values of value fields are 0

Upvotes: 0

Related Questions