Re: SQL Query Help please

> MUJAHID HAMID wrote:
>
> Dear All,
>
> I have the following select:
>
> Select account_no from table1
> where account_no not in (select account_no from table2).
>
> I am trying to say that give me the account_no's in table1 that are not in
> table2.
>
> In the result I do not get any rows back and I know for a fact that there
> are rows in table1 that are not in table2.
> (Table2 has more rows than table1)
>
> Thanks in advance
> mujahid_at_pharmco.demon.co.uk

It is extremely likely that there is at least one row in table2 which has a null account_no. This will result in no rows being returned, since the NOT IN operator is equivalent to <> ALL. Since a null value (e.g. in table2) cannot be equal *or* unequal to any value (e.g. in table1), the result is that no rows in table1 match the given criterion.

The way to avoid this problem is to use the NOT EXISTS construction:

select ... from table1
where NOT EXISTS
(select null from table2
where table2.account_no = table1.account_no);

Note that you get the same problem if you try to find employees who do not manage any employees by using:
select * from emp E1
where empno NOT IN
(select mgr from emp E2)

This also returns no rows with the standard rows in EMP. Again, the solution is to use NOT EXISTS, which is also, in most cases, more efficient.

You could also, of course, use the MINUS operator: select account_no from table1
MINUS
select account_no from table2

The NOT EXISTS operator is, however, more generally useful IMHO.

HTH Chrysalis.

--
FABRICATE DIEM PVNC
("To Protect and to Serve")
Motto of the Night Watch
Terry Pratchett - "Guards, Guards" Received on Tue Oct 20 1998 - 12:28:10 CDT