Combine result set in FULL OUTER JOIN

Question

I have two tables:

|ORDER_ID|ORDER_DATE|AMOUNT|
|00000001| 12-SEP-16|   900|
|00000002| 13-SEP-16|  1400|
|00000003| 14-SEP-16|  1000|
|00000004| 15-SEP-16|   700|

|ORDER_ID|PURCHASE_ID|PURCHASE_DATE|AMOUNT|
|00000002|   00000001|    20-SEP-16|  1450|
|00000004|   00000002|    21-SEP-16|   750|

I want the result to be like this:

|ORDER_ID|DATE     |AMOUNT_ORDER|AMOUNT_PURCHASE|
|00000001|12-SEP-16|         900|               |
|00000002|20-SEP-16|        1400|           1450|
|00000003|14-SEP-16|        1000|               |
|00000004|21-SEP-16|         700|            750|

So far I do this query:

SELECT 
  a.ORDER_ID,
  CASE WHEN b.ORDER_ID IS NULL
  THEN a.ORDER_DATE
  ELSE b.PURCHASE_DATE
  END AS DATE,
  a.AMOUNT AS AMOUNT_ORDER
  b.AMOUNT AS AMOUNT_PURCHASE
FROM TABLE_A a
FULL OUTER JOIN TABLE_B b
ON a.ORDER_ID = b.ORDER_ID

Is there another efficient way to query this?

You don't need to use Full Outer Join, you can use a simple join from table b to table a because order id in table b doesn't allow null values (That's my assumption) — H. Herzl, Sep 30 '16 at 03:22

score 0 · Answer 1 · edited May 23 '17 at 12:00

According to your requirement you can achieve same output using LEFT JOIN instead: If data must available in both tables then INNER JOIN is the best way and this is the reference where you can get overview of all the joins LEFT JOIN vs. LEFT OUTER JOIN in SQL Server hope it will be helpful.

SELECT 
  a.ORDER_ID,
  CASE WHEN b.ORDER_ID IS NULL THEN a.ORDER_DATE
  ELSE 
  b.PURCHASE_DATE
  END AS DATE,
  a.AMOUNT AS AMOUNT_ORDER
  b.AMOUNT AS AMOUNT_PURCHASE
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ORDER_ID = b.ORDER_ID

score 0 · Answer 2 · answered Sep 30 '16 at 03:38

if you want Full result set from both table then use full outer join.

                select 
                a.ORDER_ID,
                coalesce (a.order_date,b.purchase_date) as DATE,
                a.AMOUNT AS AMOUNT_ORDER,
                b.AMOUNT AS AMOUNT_PURCHASE
                from #table_a a
                full outer join #table_b b on b.ORDER_ID=a.ORDER_ID 
                order by  a.ORDER_ID

if you want only result from Table_a use Left join.

            select 
            a.ORDER_ID,
            coalesce (a.order_date,b.purchase_date) as DATE,
            a.AMOUNT AS AMOUNT_ORDER,
            b.AMOUNT AS AMOUNT_PURCHASE
            from #table_a a
            left join #table_b b on b.ORDER_ID=a.ORDER_ID 
            order by  a.ORDER_ID

score 0 · Answer 3 · edited Sep 30 '16 at 09:17

0

SELECT a.ORDER_ID
    ,CASE 
        WHEN b.ORDER_ID IS NULL
            THEN a.ORDER_DATE
        ELSE b.PURCHASE_DATE
        END AS DATE
    ,a.AMOUNT AS AMOUNT_ORDER b.AMOUNT AS AMOUNT_PURCHASE
FROM TABLE_A a
RIGHT JOIN TABLE_B b ON a.ORDER_ID = b.ORDER_ID

edited Sep 30 '16 at 09:17

Unnikrishnan R

4,965
1
12
21

answered Sep 30 '16 at 07:27

fahad

154
6

Combine result set in FULL OUTER JOIN

3 Answers3