Doubt in SQL query

Replies to This Discussion

Permalink Reply by Nanda on March 24, 2009 at 9:44pm

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.

Permalink Reply by Esmaiel on March 4, 2011 at 4:25pm

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

Permalink Reply by Manabendra on November 7, 2009 at 9:27pm

"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.