Oracle – Outer Join with join condition

I have a scenario where I have two tables

ORDER

ORDER_NO
LOCATION
ITEM
QTY_RECEIVED

SHIPMENT

ORDER_NO
LOCATION
ITEM
QTY_RECEIVED

There are cases where ORDER table has a record but SHIPMENT table doesn’t

I want all the rows from ORDER table where the qty is not equal to the qty in SHIPMENT table, and that will include the rows which are there in ORDER but not in shipment.

I tried doing by this:

SELECT 
    order_no, item, location, SUM(NVL(QTY_RECEIVED, 0)) 
FROM
    ORDERS ol                     
GROUP BY 
    ORDER_NO, ITEM, LOCATION
HAVING 
    SUM (NVL(ol.QTY_RECEIVED,0)) <>      

    (SELECT SUM(NVL(sk.QTY_RECEIVED, 0))
     FROM shipment s
     WHERE s.order_no = ol.order_no
       AND s.item (+)= ol.item
       AND s.location (+) = ol.location
     GROUP BY s.order_no, s.item, s.location);

But it doesn’t give the correct result.

how should I do this?

Answer

You need LEFT JOIN in order to return the the results even for the non-existing values of SHIPMENT table :

SELECT ol.order_no, ol.item, ol.location, 
       SUM(NVL(ol.QTY_RECEIVED, 0)) AS "Total Quantity Of Orders",
       SUM (NVL(s.QTY_RECEIVED,0))  AS "Total Quantity Of Shpm." 
  FROM orders ol 
  LEFT JOIN shipment s  
    ON s.order_no = ol.order_no
   AND s.item = ol.item
   AND s.location = ol.location
 GROUP BY ol.order_no, ol.item, ol.location
HAVING SUM (NVL(ol.QTY_RECEIVED,0)) <> SUM (NVL(s.QTY_RECEIVED,0))