Search code examples
sqlselectdb2query-optimization

How to tell SQL to return TRUE on "positive value occurance" rather than using SUM(field) >0 condition

I am doing aggregation on QTY field which will have either 0 or positive value. So presence of any positive number can predict sum will be greater than 0. Is there any way to return true once a positive value found rather than doing summation of all rows ? It is DB2 database.

 select *
    from a
    where a_id in (1,2,3,4)
    and
    (
      flag = 'Y' 
      or 
      (select sum(qty) from b where b.a_id = a.a_id) > 0
    )
Solution

  • Is it any faster with EXISTS?

     select *
    from a
    where a_id in (1,2,3,4)
    and
    (
      flag = 'Y' 
      or 
      EXISTS (select b.a_id from b where b.a_id = a.a_id and qty>0)
    )