Re: SQL query
- From: Christopher Boyle <cboyle@xxxxxxxxxxxxxxxxxxxxxx>
- To: igor.neyman@xxxxxxxxx
- Date: Mon, 12 Apr 2010 15:31:49 -0400
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.
>>
>>
>>
>
Other related posts:
