hi

I have troubles in doing SQL request comparison. I need to know if 2 sql request are equal. I think I need some logical language as Isabelle/HOL to find if 2 SQL request do the same result...
the syntaxical and grammatical analysis are not enough because 2 different request can give the same result. Moreover 2 requests can give the same results for a set of data and can give different result for another set of data... so comparing the request or their results in not enaugh to say that two requests are the same...

does anyone know how I can do this any tips to help me?

thank you

try to use logical operations like MINUS, UNION and INTERSECT.

e.g.

select * from tab1
MINUS
select * from tab2

would return every record in tab1 that is not in tab2


select * from tab1
INTERSECT
select * from tab2

would return all records that are present in both queries.


Something like

(select * from tab1
 UNION
 select * from tab2
)
 MINUS
(select * from tab1
 INTERSECT
 select * from tab2
);

should do the complete job and return every record that was not returned by both queries.

dunno if this is standard sql but it works well and very fast with oracle.

UNION and INTERSECT are SQL 92 compliant, I don't know about MINUS though. There is a SQL 92 compliant way of doing it but I am not sure if MINUS is the appropiate one, it might be though.

Regards
Owain Williams

EXCEPT is the SQL-92 equivalent of Oracle's MINUS, I believe.

(Not many RDBMS's support INTERSECT and EXCEPT, though...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com