Hi Jobin and Andrew, Thanks for your suggestions. Minus/Except and Intersect are indeed quite easy to implement using the sub-queries ( for eg left join as you suggested) but I am sure you would have faced a hell lot of trouble in conjecturing a "DIVIDE" query. I do hope you have used a "DIVIDE" implementation in a query before. Only then can you understand the complexity of the queries involving this operation.
Thanking you, Akshay Mittal IIT Kanpur, India On Tue, Mar 30, 2010 at 12:07 AM, Jobin Augustine <[email protected]> wrote: > Andrew, you are correct. > A simple left Join is sufficient. and no need of any sub-query to do minus. > just like : > > select t1.a,t1.b > from t1 left outer join t2 on t1.a = t2.a and t1.b = t2.b > where t2.a is null... > > and if user don't know what is left join, can be more explicit: > > select t1.a,t1.b from t1 where (t1.a,t1.b) not in (select t2.a,t2.b from > t2); > here "not in" = "minus" > > The first one method is much better because it is a normal join operation > it can do all optimization. > on the otherhand a minus operation not only cannot be optimized (say, for > using indexes), need to store the result in temporarily just like Union. > > IMHO, the keyword "minus" is a Oraclism. not in standards and other > database like Postgres, M$ SQL. > Standard keywords for set operations are : UNION, INTERSECT, and EXCEPT. > here EXCEPT is "minus" > INTERSECT is much more straight to put in normal join. > > In all these set operations, UNION is required to be implemented and > drizzle already has it. > others are just of decorative bells. > > Thank you, > Jobin. > > On Mon, Mar 29, 2010 at 7:15 PM, Andrew Hutchings > <[email protected]>wrote: > >> Hello Akshay, >> >> On Mon, 2010-03-29 at 18:53 +0530, Akshay Mittal wrote: >> > MySql does not have the set operations-- >> > "divide" , "intersect" and "minus" --- related queries as opposed to >> > set operation - "union". Recently I have developed a php >> > wrapper which could interface with MySql and implement the "divide" , >> > "intersect" and "minus" features in the same way as "union". For >> > example: >> > >> > select A,B from rel1 minus select A,B from rel2 >> > >> > will return the tuples(A,B) that are present in rel1 and not present >> > in rel2. >> >> I could be wrong but is this not the same as a left join and checking >> for NULL in the where condition? >> >> Kind Regards >> -- >> Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/ >> Certified MySQL Developer, DBA & Cluster DBA >> Zend PHP5 Certified Engineer >> >> >> _______________________________________________ >> Mailing list: >> https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> >> Post to : [email protected] >> Unsubscribe : >> https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> >> More help : https://help.launchpad.net/ListHelp >> > >
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
