Difference between the data in Table A and Table B

Question

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 rows in Table A that are not present in Table B.

Expected Output : 
141 , PL_10122016, USD

Srini V · Accepted Answer · 2016-06-27T14:23:02.603

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

The Minus somehow does not do the Job. :-( – arjun Jun 27 '16 at 14:03 — arjun, Jun 27 '16 at 14:03
I have added some sample data. – arjun Jun 27 '16 at 14:17 — arjun, Jun 27 '16 at 14:17

score 0 · Answer 2 · edited May 23 '17 at 11:44

Have you searched stackoverflow before asking? There are already answers to similar questions. Take a look here.

His solution:

a1 <- data.frame(a = 1:5, b=letters[1:5])
a2 <- data.frame(a = 1:3, b=letters[1:3])

rows.in.a1.that.are.not.in.a2  <- function(a1,a2)
{
    a1.vec <- apply(a1, 1, paste, collapse = "")
    a2.vec <- apply(a2, 1, paste, collapse = "")
    a1.without.a2.rows <- a1[!a1.vec %in% a2.vec,]
    return(a1.without.a2.rows)
}
rows.in.a1.that.are.not.in.a2(a1,a2)

C. Price · Answer 3 · 2016-06-27T14:34:30.243

Select A.price, A.pricelist, A.currency
    FROM TableA A 
    LEFT JOIN TableB B
      ON  A.price = B.price  
      AND A.pricelist = B.pricelist
      AND A.currency = B.currency

Until here you have matching rows from TableB and all rows from TableA (matching and non-matching) . The followig condition will reduce the results to only non-matching rows:

      WHERE B.price is NULL
      AND B.pricelist is NULL
      AND B.currency is NULL

Difference between the data in Table A and Table B

3 Answers3