SQL EXISTS || DB2 for i SQL

EXISTS predicate in Search condition in DB2 for i SQL

It is used to test the existence of certain rows. For Example, refer to the Customer table and find out all the customer ids whose COUNTRY is USA.

select 1  from sysibm.sysdummy1                      
where EXISTS                                         
(SELECT * FROM customer WHERE CUSTCOUNTRY = 'USA' )  

Output:

Constant value                 
********  End of data  ********

Also, we can test if certain rows not exist. So just use NOT EXISTS in above example/

select 1  from sysibm.sysdummy1                      
where NOT EXISTS                                         
(SELECT * FROM customer WHERE CUSTCOUNTRY = 'USA' )  

Output:

Constant value                  
             1                  
********  End of data  ********