Compare two SELECT statement using PL/SQL Developer

Question

Suppose I have two SELECTquery on same table and I need to compare them column by column.

Query 1:

select * from mytable t1 where  t1.code = '100'

returns:

id           name
1            A
2            B

Query 2:

select * from mytable t2 where  t2.code = '999'

returns:

id           name
1            A
2            C

For my case both query returns same number of rows.

Desired result

id           name_t1     name_t2
2            B           C

How can I found the data difference between them using PL/SQL developer (better using tools to avoid query)?

My PL/SQL Developer Version 8.0.3.1510

Solution 1

you can use MINUS

select * from mytable t1 where  t1.code = '100'

MINUS 
select * from mytable t2 where  t2.code = '999';

MINUS gives you the rows that are found in the first query and not in the second query by removing from the results all the rows that are found only in the second query

Solution 2

There are many ways you can check the difference between two queries; if you need differences betwee dataset, you can try MINUS:

SQL> select * from mytable t1 where  t1.code = '100'
  2  minus
  3  select * from mytable t2 where  t2.code = '999';

  ID CODE NAME
---- ---- ----------
   1  100 A
   2  100 B

SQL> select * from mytable t2 where  t2.code = '999'
  2  minus
  3  select * from mytable t1 where  t1.code = '100';

  ID CODE NAME
---- ---- ----------
   1  999 A
   2  999 C

By combining two MINUS, you can have T1-T2 AND T2-T1:

SQL> select 'T1 MINUS T2' TYPE, t1_t2.* from
  2  (
  3      select * from mytable t1 where  t1.code = '100'
  4      minus
  5      select * from mytable t2 where  t2.code = '999'
  6  ) t1_t2
  7  union all
  8  select 'T2 MINUS T1' TYPE, t2_t1.* from
  9  (
 10      select * from mytable t2 where  t2.code = '999'
 11      minus
 12      select * from mytable t1 where  t1.code = '100'
 13  ) t2_t1;

TYPE          ID CODE NAME
----------- ---- ---- ----------
T1 MINUS T2    1  100 A
T1 MINUS T2    2  100 B
T2 MINUS T1    1  999 A
T2 MINUS T1    2  999 C

If you need to check field differences, based on a 'key' field, you need a JOIN:

SQL> select  id, t1.name as name_t1, t2.name as name_t2
  2  from myTable t1
  3    inner join myTable t2 using(id)
  4  where t1.name != t2.name
  5    and t1.code = '100'
  6    and t2.code = '999';

  ID NAME_T1    NAME_T2
---- ---------- ----------
   2 B          C