468,257 Members | 1,509 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,257 developers. It's quick & easy.

"where not in " doesn't work

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
4 28280
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
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
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
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.

Similar topics

25 posts views Thread by delerious | last post: by
2 posts views Thread by Ellen Manning | last post: by
6 posts views Thread by **Developer** | last post: by
94 posts views Thread by Samuel R. Neff | last post: by
19 posts views Thread by Daniel Pitts | last post: by
19 posts views Thread by maya | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.