Re: subquery vs set operator

From: Ortwin Glück <glueck_at_freesurf.ch>
Date: Fri, 08 Sep 2000 22:02:00 +0200
Message-ID: <39B945B8.46DC2F81@freesurf.ch>

1
2
3
4
5
6
7

4
5
6
7
8
9
10

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

8,9,10

Select employee_no from table A
Minus
Select employee_no from table B;

1,2,3

The queries are completely different!!!
So if table B has NO records 8,9,10 your first query is empty.

O. Glueck
DataChick wrote:
>
> 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 Fri Sep 08 2000 - 15:02:00 CDT