arjun arjun - 5 months ago 13
SQL Question

Difference between the data in TAble A and TAble B

Let us assume we are using Oracle Database:

Table A has columns: price, pricelist, currency.
Table B has columns: price, pricelist, currency, status.

sample Data :

Table A

price, pricelist, currency
123 , PL_10122016, EUR
111 , PL_10122016, GBP
141 , PL_10122016, USD

Table B

price, pricelist, currency,status
123 , PL_10122016, EUR,0
111 , PL_10122016, GBP,0
143 , PL_10122016, USD,0


I would like to find the columns in Table A that are not present in Table B.

Expected Output :
141 , PL_10122016, USD

Answer

using Minus

WITH tablea  (price, pricelist, currency) AS
(SELECT 123 , 'pl_10122016', 'eur' FROM dual UNION ALL
SELECT 111 , 'pl_10122016', 'gbp' FROM dual  UNION ALL
SELECT 141 , 'pl_10122016', 'usd' FROM dual )
,tableb (price, pricelist, currency,status) AS 
(SELECT 123 , 'pl_10122016', 'eur',0 FROM dual  UNION ALL
SELECT 111 , 'pl_10122016', 'gbp',0  FROM dual UNION ALL
SELECT 143 , 'pl_10122016', 'usd',0 FROM dual )
SELECT price , pricelist , currency  FROM tablea
MINUS
SELECT price , pricelist , currency  FROM tableb;

Result:
PRICE   PRICELIST   CURRENCY
141 pl_10122016 usd

using Left join

WITH tablea  (price, pricelist, currency) AS
(SELECT 123 , 'pl_10122016', 'eur' FROM dual UNION ALL
SELECT 111 , 'pl_10122016', 'gbp' FROM dual  UNION ALL
SELECT 141 , 'pl_10122016', 'usd' FROM dual )
,tableb (price, pricelist, currency,status) AS 
(SELECT 123 , 'pl_10122016', 'eur',0 FROM dual  UNION ALL
SELECT 111 , 'pl_10122016', 'gbp',0  FROM dual UNION ALL
SELECT 143 , 'pl_10122016', 'usd',0 FROM dual )
SELECT a.*
FROM tablea a 
LEFT JOIN tableb b
  ON  a.price = b.price AND a.pricelist = b.pricelist AND a.currency = b.currency
  WHERE b.price IS NULL AND b.pricelist IS NULL AND b.currency IS NULL;

Result:
PRICE   PRICELIST   CURRENCY
141 pl_10122016 usd

You can do the same with RIGHT JOIN as well as FULL OUTER JOIN