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
