By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

"where not in " doesn't work

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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'
Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.