Re: odd sub-select problem

Assuming what you submitted has typos (you never specify table B), don't feel bad. Everyone always misses the obvious in this problem. Try:

SELECT name FROM A
MINUS
SELECT name FROM B

This will show all the names in A that are not in B.

Anyway, in what you submitted, the first querey better return 0 rows. Your are selecting from A where not in A, or in other terms A-A=0

The 2nd query as written should return all the rows in A. A+0=A

In any event, go with the MINUS. It's faster and easier.

"Daniel Schmolze" <usenet_at_schmolze.com> wrote in message news:12bb17fa.0110291011.32821eff_at_posting.google.com...
> I'm trying to do something which I think is very simple. I have a
> table, A, which contains about 13000 rows, and a table B which
> contains about 1600 rows.
>
> Both these tables have a NAME column in the same domain. I want to
> display the NAMEs in A which are not in B. To do this, I'm using the
> following query:
>
> SELECT NAME FROM A
> WHERE NAME NOT IN (SELECT NAME FROM A)
>
> This returns no rows. If, however, I do the following:
>
> SELECT NAME FROM A
> WHERE NAME IN (SELECT NAME FROM A)
>
> (In other words, show me all the names in A that *are* in B)
>
> I get about 1500 rows, as expected. Can anyone shed some light on
> this?
>
> Thanks.
Received on Mon Oct 29 2001 - 12:24:24 CST