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

I think, only way could be a [export to excel option](http://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer). [`MINUS`](http://www.techonthenet.com/sql/minus.php) might favor you as well. — Maheswaran Ravisankar, Mar 29 '16 at 06:16

score 8 · Answer 1 · edited Mar 29 '16 at 06:19

8

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

edited Mar 29 '16 at 06:19

answered Mar 29 '16 at 06:18

Ramki

453
2
7

For my case both query returns same number of rows. and need to compare them cascade – Mar 29 '16 at 06:23
Hi, you want to compare number of rows ? of the difference in data – Ramki Mar 29 '16 at 06:26
compare column by column data – Mar 29 '16 at 06:28
hi, Then MINUS should be good , try with your table and post the result . – Ramki Mar 29 '16 at 06:30

score 4 · Answer 2 · answered Mar 29 '16 at 08:38

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

score 0 · Answer 3 · answered Mar 29 '16 at 11:34

0

select id, t1.name as name_t1, t2.name as name_t2 from myTable t1 Right join myTable t2 Right join t1.name=t2.name where t1.name is null

answered Mar 29 '16 at 11:34

Rohit Gupta

455
4
16

Compare two SELECT statement using PL/SQL Developer

3 Answers3