Just out of curiosity, what is the business case for not using NOT or MINUS?... On Mon, Apr 12, 2010 at 3:25 PM, Igor Neyman <igor.neyman@xxxxxxxxx> wrote: > You were given the right answer: > > SELECT col1 FROM test1 LEFT OUTER JOIN test2 ON (col1=col3) WHERE col3 IS > NULL; > > Why wouldn't you try it before rejecting it? > > Igor Neyman > > > On Mon, Apr 12, 2010 at 3:14 PM, <Mayen.Shah@xxxxxxxxxx> wrote: > >> >> May be it was not clear in my question but simple join will give me rows >> from test1 that matches with rows in test2 table. >> I want to get rows from test1 table that do not match with rows in test2 >> on col1 and col3. Again I can not use NOT or MINUS. >> >> Outer join also has similar problem. >> >> Thanks >> Mayen >> >> >> >> >> >> *Michael.Coll-Barth@xxxxxxxxxxxxxxxxxxx* >> Sent by: oracle-l-bounce@xxxxxxxxxxxxx >> >> Apr 12 2010 03:01 PM >> Please respond to >> Michael.Coll-Barth@xxxxxxxxxxxxxxxxxxx >> >> To >> ORACLE-L <oracle-l@xxxxxxxxxxxxx> >> cc >> Subject >> RE: SQL query >> >> >> Hint: Join the tables on COL1=COL3 >> >> ------------------------------ >> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: >> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mayen.Shah@xxxxxxxxxx* >> Sent:* Monday, April 12, 2010 2:57 PM* >> To:* ORACLE-L* >> Subject:* SQL query >> >> >> Hi Listers, >> >> One of my colleagues has challenged me with SQL question. I can not figure >> out how to do this. >> >> SQL> select * from test1; >> >> COL1 COL2 >> ---------- ---------- >> 11 21 >> 12 22 >> 13 23 >> 14 25 >> >> 4 rows selected. >> >> SQL> select * from test2; >> >> COL3 COL4 >> ---------- ---------- >> 41 51 >> 42 52 >> 43 53 >> 13 23 >> 14 25 >> >> 5 rows selected. >> >> Here is simple query. >> >> SQL> select col1 from test1 where col1 not in (select col3 from test2); >> >> COL1 >> ---------- >> 11 >> 12 >> >> 2 rows selected. >> >> I have to rewrite this query where I can't use NOT or MINUS. >> >> Can anyone help me? >> >> Thanks >> Mayen >> The information contained in this message and any attachment may be >> proprietary, confidential, and privileged or subject to the work >> product doctrine and thus protected from disclosure. If the reader >> of this message is not the intended recipient, or an employee or >> agent responsible for delivering this message to the intended >> recipient, you are hereby notified that any dissemination, >> distribution or copying of this communication is strictly prohibited. >> If you have received this communication in error, please notify me >> immediately by replying to this message and deleting it and all >> copies and backups thereof. Thank you. >> >> >> >