Geek Answers Handbook

Compare entries from two tables and highlight Diferences

I have two tables with exactly the same columns

Table a

  id FLAG
 1 Y
 2 Y
 3 N
 4 N

Table B

  id FLAG
 1 Y
 2
 3 N
 4

I would like to write a select query in Oracle that will display all the rows in table A, where the FLAG column does not match table B, but has corresponding identifier columns

The result should be as follows.

  id FLAG
 2 Y
 4 N
+4
source share
7 answers

"all rows in table A ... as table B with corresponding identifier columns":

 FROM a JOIN b USING (id)

"where the FLAG column is not the same:

 WHERE a.flag != b.flag OR (a.flag IS NULL AND b.flag IS NOT NULL) OR (b.flag IS NULL AND a.flag IS NOT NULL)

So the request will be

 SELECT id, a.flag FROM a JOIN b USING (id) WHERE a.flag != b.flag OR (a.flag IS NULL AND b.flag IS NOT NULL) OR (b.flag IS NULL AND a.flag IS NOT NULL)

The WHERE is ugly, but you need to catch cases where table b is NULL but not, or vice versa ...

+3
source

this one works in the oral language .. since the columns have zero. It cannot be compared directly, so you need to use NVL () or an equivalent function

 select a1.* from table_a a1,table_b b where A1.id=B.id and nvl(a1.flag,'y')<>nvl(b.flag,'y')

see this sqlfiddle: http://sqlfiddle.com/#!4/241de/1

+2
source

It will be done

  select Table_A.id,Table_A.FLAG from Table_A join Table_b on Table_A.id=Table_b.id where Table_A.FLAG!=Table_b.FLAG or (Table_b.FLAG is null and table_a.flag is not null);

sqlfiddle

+1
source

What about

  SELECT * FROM TABLEA INNER JOIN TABLEB ON TABLEA.ID = TABLEB.ID WHERE TABLEA.Flag != TABLEB.FLAG OR TABLEB.FLAG IS NULL

Demo

0
source
  • What if the row is in TAble A and not in table B? do you still want to display it? This will help you decide whether you want to make an inner join or an outer join.

Assuming this is so, you can get the details using ..

 select a.id, a.flag, b.flag from table_a a, table_b b where a.id = b.id(+)

Since you need strings where the flag is not equal.

 select a.id, a.flag, b.flag from table_a a, table_b b where a.id = b.id(+) and a.flag <> b.flag

Also check if the flag can be null, in which case you may need to modify the request a bit.

0
source 
 SELECT a.* FROM tableA a LEFT JOIN tableB b ON a.id = b.ID AND a.FLAG = b.FLAG WHERE b.ID IS NULL
0
source

It seemed to me that you were joking :) In such cases, Oracle has a special MINUS operator. Link to evidence

So the answer is very short and simple

 select id, flag from a minus select id, flag from b

SQLFiddle

PS Sorry - or should there be entries with the corresponding identifier in table B? Then see below

 select id, flag from a where id in (select id from b) minus select id, flag from b

SQLFiddle

0
source

More articles:


All Articles