NOT IN Query Returns No Results -- sql server mseq

Baner
9/17/2004 9:14:58 AM

When running the following query and subquery with a NOT
IN clause, I'm getting no results with SQL Server 2000
SP3. Has anyone seen this before ?
=======================================
select * from T_TEST_PB_091704
where acct_or_lvl_nm not in (
select distinct PAR_MEMB_ALIAS_TXT CACCT_OR_LVL_NM
from t_temp_acct_hier_rap_new3
)

There are 6 values in table T_TEST_PB_091704. 4 of these
exist in table t_temp_acct_hier_rap_new3.
I get the right results if:
a) I hard code the select list in the subquery by:
select * from T_TEST_PB_091704
where acct_or_lvl_nm not in (
select distinct PAR_MEMB_ALIAS_TXT
from t_temp_acct_hier_rap_new3
where par_memb_alias_txt in (
'BOOK_BALANCE_AB',
'COMML_LOANS_AB',
'COMML_R_E_AB',
'MTG_BCK_SEC_AB',
'MTG_SVC_RHTS_AB',
'FLOAT_BALANCE_AB' )
)

or, if I use T-SQL with an outer join:
select DISTINCT a.acct_or_lvl_nm
from dbo.T_TEST_PB_091704 a LEFT OUTER join
dbo.t_temp_acct_hier_rap_new3 b
ON a.acct_or_lvl_nm=b.par_memb_alias_txt
WHERE b.Par_Memb_Alias_Txt is null

--------
MTG_BCK_SEC_AB
MTG_SVC_RHTS_AB

or, if I use a NOT EXISTS clause:
select * from T_TEST_PB_091704 a
where NOT EXISTS
( select distinct PAR_MEMB_ALIAS_TXT CACCT_OR_LVL_NM
from t_temp_acct_hier_rap_new3 b
where a.acct_or_lvl_nm=b.par_memb_alias_txt )

--------
MTG_BCK_SEC_AB
MTG_SVC_RHTS_AB

Thanks in advance

Baner
9/17/2004 10:12:29 AM

Thanks Vishal. I guess I just have to remember use the IS
NOT NULL clause in the subquery or use the NOT EXISTS
[quoted text, click to view]

Vishal Parkar
9/17/2004 10:01:27 PM

use "not exists"

Internally in and exists clauses are evaluated differently. In clause is
internally evaluated as multiple OR conditions. Whereas EXISTS
clause checks for the subquery returns at least one row.

This is typical scenario when checking non-existing rows from the table.

select * from customers
where customerid not in(select customerid from orders union all select null)

If any of the rows from the subquery returns null value and if you are using
NOT IN clause then you will get any empty resultset.

You can use NOT EXISTS clause to get the required result set.

same query can be re-written as

select * from customers
where not exists
(select * from
(select distinct customerid from orders union all select null) a
where a.customerid = customers.customerid
)

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : NOT IN Query Returns No Results