How to compare if two sets are equal.

[CapsuleCorpJX]

I want to see if the result of one select is excatly the same as the result of another select.

I can test if one set is a subset of another by doing a Minus, and I guess two minus's will show if the two sets are equal.

But is there a more efficient way of doing this?

 

[teradev]

Hi

please check the following link for the solution, similar to your question.

http://www.tek-tips.com/viewthread.cfm?qid=919700

good luck

 

[WILLIEWANKA]

if this has to be done outside of bteq, you can use something like the following

select
case when b.cnt = 0 then 'Identical' else 'Different' end as yayornay
from
(
select count (*) from
(select * from table1
minus all
select * from table2
union all
select * from table2
minus all
select * form table1) a
) b





Dieter, I saw that you had used "where id in (select id...." in the post teradev linked to.

Doesn't that do a full table scan, instead of using the index? Is there any performance difference with using "minus" over "in/not in", or even using a left outer join where b.column is null?

 

[CapsuleCorpJX]

I guess a double minus (both sets are subsets of the other) is the best way, and this is what I'm using now.

I was just wondering if there was some pre-made function that tests it.

 

[dnoeth]

WILLIEWANKA:
"Dieter, I saw that you had used "where id in (select id...." in the post teradev linked to."

I just used it because i cut'n'pasted teradev's code ;-)

There's no difference between IN/EXISTS, but there may be a huge difference between NOT IN/NOT EXISTS:
If both columns are NOT NULL, it's the same plan, but if not, then NOT EXISTS is much better.

But MINUS/EXCEPT ALL is easier to write and works with MultiSets...

Outer Join with "col IS NULL" is slower, because there's an extra Retrieve step.


If both answer sets return a distinct set and all columns are NOT NULL a Full Outer Join might be used, too:

select *
from set1 full join set2 on all columns
where set1.col is null
or set2.col is null

Dieter

 

[bunwong]

intersect" the 2 tables. the are identical if the result row count is the same as the 2 source tables (of course the 2 source tables already has same number of rows, otherwise you can directly jump to the conclusion that they are different)