"where not in " doesn't work

[Jacinle Young]

Hi all,

I have written the SQL but it doesn't work

db2 "select acode from t1 where (acode) not in (select eid from t2
where iid ='0') and iid ='0'"

select nothing
but the following works

db2 "select acode from t1 where not exists (select * from t2 where iid
='0' and t1.acode=t2.eid) and crm.eod.institutionid ='0'"

I always thought where not in is equal to where not exists

anything went wrong?

Jacinle

[Philip Sherman]

NOT EXISTS can yield different results from NOT IN.

NOT IN implies that data exists in the column. A predicate testing a
column containing a NULL value will not satisfy the NOT IN condition.
(verified on UDB LUW 8.1)

A null value in acode will satisfy the NOT EXISTS clause in your second
example. This is true even if t2 has a row containing a null value in eid!
Phil Sherman
Jacinle Young wrote:

Hi all,

I have written the SQL but it doesn't work

db2 "select acode from t1 where (acode) not in (select eid from t2
where iid ='0') and iid ='0'"

select nothing
but the following works

db2 "select acode from t1 where not exists (select * from t2 where iid
='0' and t1.acode=t2.eid) and crm.eod.institutionid ='0'"

I always thought where not in is equal to where not exists

anything went wrong?

Jacinle

[Jacinle Young]

I find that the performance of "not exists" is too slow

Is there any other syntax?

like minus in Oracle?

Philip Sherman <ps******@ameritech.net> wrote in message news:<T5***************@newssvr16.news.prodigy.com >...

NOT EXISTS can yield different results from NOT IN.

NOT IN implies that data exists in the column. A predicate testing a
column containing a NULL value will not satisfy the NOT IN condition.
(verified on UDB LUW 8.1)

A null value in acode will satisfy the NOT EXISTS clause in your second
example. This is true even if t2 has a row containing a null value in eid!
Phil Sherman
Jacinle Young wrote:

Hi all,

I have written the SQL but it doesn't work

db2 "select acode from t1 where (acode) not in (select eid from t2
where iid ='0') and iid ='0'"

select nothing
but the following works

db2 "select acode from t1 where not exists (select * from t2 where iid
='0' and t1.acode=t2.eid) and crm.eod.institutionid ='0'"

I always thought where not in is equal to where not exists

anything went wrong?

Jacinle

[Theo Peterbroers]

ja***********@hongkong.com (Jacinle Young) wrote in message news:<12**************************@posting.google. com>...

I find that the performance of "not exists" is too slow

Is there any other syntax?

like minus in Oracle?

Philip Sherman <ps******@ameritech.net> wrote in message news:<T5***************@newssvr16.news.prodigy.com >...

NOT EXISTS can yield different results from NOT IN.

NOT IN implies that data exists in the column. A predicate testing a
column containing a NULL value will not satisfy the NOT IN condition.
(verified on UDB LUW 8.1)

A null value in acode will satisfy the NOT EXISTS clause in your second
example. This is true even if t2 has a row containing a null value in eid!
Phil Sherman
Jacinle Young wrote:

Hi all,

I have written the SQL but it doesn't work

db2 "select acode from t1 where (acode) not in (select eid from t2
where iid ='0') and iid ='0'"

select nothing
but the following works

db2 "select acode from t1 where not exists (select * from t2 where iid
='0' and t1.acode=t2.eid) and crm.eod.institutionid ='0'"

I always thought where not in is equal to where not exists

anything went wrong?

Jacinle

SELECT acode from t1 where ...
EXCEPT
SELECT eid from t2 where iid='0'

[Mark Yudkin]

Use EXPLAIN to find out why your performance is poor. It can usually be
tracked down to missing indices or poor statistics. "Not exists" used to be
slow, but IBM added semijoin processing to DB2 a while back.

Changing the syntax will usually have no effect on performance issues in
DB2.

"Jacinle Young" <ja***********@hongkong.com> wrote in message
news:12**************************@posting.google.c om...

I find that the performance of "not exists" is too slow

Is there any other syntax?

like minus in Oracle?

Philip Sherman <ps******@ameritech.net> wrote in message

news:<T5***************@newssvr16.news.prodigy.com >...

NOT EXISTS can yield different results from NOT IN.

NOT IN implies that data exists in the column. A predicate testing a
column containing a NULL value will not satisfy the NOT IN condition.
(verified on UDB LUW 8.1)

A null value in acode will satisfy the NOT EXISTS clause in your second
example. This is true even if t2 has a row containing a null value in eid!

Phil Sherman
Jacinle Young wrote:

Hi all,

I have written the SQL but it doesn't work

db2 "select acode from t1 where (acode) not in (select eid from t2
where iid ='0') and iid ='0'"

select nothing
but the following works

db2 "select acode from t1 where not exists (select * from t2 where iid
='0' and t1.acode=t2.eid) and crm.eod.institutionid ='0'"

I always thought where not in is equal to where not exists

anything went wrong?

Jacinle