Re: subquery vs set operator

From: Zbigniew Sliwa <zibi_at_at_hotmail.com>
Date: Mon, 11 Sep 2000 10:27:34 +0200
Message-ID: <8pi500$2kb$1@news.tpi.pl>

These statements should generate the same results.

Select employee_no from B
where employee_no not in
(select employee_no from A);

Select employee_no from table B
Minus
Select employee_no from table A

When using MINUS then you have to take care about the order of compound queries. MINUS operator will return all distinct rows selected by the first query but not the second.

---

Regards,

Zbigniew Sliwa
Oracle Programmer
Poland
email: zibi_at_at_yahoo.com

DataChick napisa³(a) w wiadomo¶ci: <8pb8sh$740$1_at_nnrp1.deja.com>...
>I was having trouble getting a set of values using a subquery, but was
>sucessful with using a set operator. Although the problem is solved, I
>am curious as to why the first solution did not work.
>I have two tables, A and B. both use objid as a PK, and both have a
>column for employee_no. There are rows in table A that do not have a
>corresponding row in B.
>
>When I tried this:
>Select employee_no from B
>where employee_no not in
>(select employee_no from A);
>
>This returns no rows.
>However, when rewritten as:
>
>Select employee_no from table A
>Minus
>Select employee_no from table B;
>
>I get the correct values.
>Can anyone help me figure out why the first query is not working? Is
>there a logic error here? Three of us in my company who are fairly
>experienced with SQL are stumped by this one.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Sep 11 2000 - 03:27:34 CDT