How to check equality of two SQL select result sets in Oracle

Often you need to know if the new SQL select statement is equivalent to old one. Let’s say You rewrite select and it’s much faster, but is it equivalent to old one? You need to compare result sets of old and new select statements. You can compare result sets with different combinations of bind variables. If you don’t have any bind variables, create them by replacing constant values. If results sets are equal in all test scenarios you are good to go. Probability that new SQL select statement is equivalent to old one is greater, if you test selects with more different combinations of bind variables and if result sets are bigger.

Select for comparing old select with new select is:

WITH old_select as (select …)

,new_select as (select …)

select count(*), ‘diffs’ description from

 (

         (

                select * from old_select

                        minus

                 select * from new_select

             )

             union

             (

                    select * from new_select

                        minus

                    select * from old_select

                )

  )

  union all

  select count(*), 'new_select rows' description from new_select   

  union all

  select count(*), 'old_select rows' description from old_select   

 ;

Following result set means that result sets are equal and that there are 130 rows in each result set.

0      ‘diffs’

130    'new_select rows'

130    'old_select rows'