ChannelDB2

Hai,

I have a query like below

select cltname from clt00 where not exists(select 1 from cln00);

clt00 contains the below data
vijay
sankar
gopi
cln00 contains below data
vijay
sankar
vijji

can you please tell what is the output of the above query?
and please explain what that query is doing.

Views: 30

Replies to This Discussion

The output should not contain any rows at all. As you have a NOT EXISTS in the WHERE clause which always return value '1' (as the table CLN00 contains data), the predicate will fail resulting in not finding any valid rows in the outer query.

On my server which is AIX OS LEVEL 6.1.0 & DB2 9.7.1 , this SQL works as designed:

1.) No rows returned:

hctunx196 GEMSDEV >db2 "select creator,name from sysibm.systables where not exists (select 1 from sysibm.systablespaces)"

CREATOR                                                                                                                          NAME                       
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

  0 record(s) selected.

 

2.)

  421 record(s) selected.

hctunx196 GEMSDEV >db2 "select creator,name from sysibm.systables where not exists (select * from sysibm.systablespaces)"

CREATOR

"select 1 from cln00" always it is true. After that, it is "NOT EXIST", it means that 1 will be 0, so the where clause turns false. So, it should not select any row.

RSS

Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service