Using ALL

• ron k

  SSCertifiable

  Points: 6103

  More actions

  May 27, 2005 at 12:31 pm

  #67086

  use pubs

  go

  if 99 = All (Select pub_id from publishers where pub_id = -1)

   print 'All equal'

  else

   print 'not all equal'

  In the above example no rows are returned by the select but the "result" is 'All equal'.  Why? And is there a way around this?

  Thanks,

  ron

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  May 27, 2005 at 12:55 pm

  #561572

  This is interesting. It returns false only when the sub query violates the condition. When it is blank it returns true.

  There is a work around. I wouldn't say it is elegant.

  if 99 = All (Select pub_id from publishers where pub_id = -1

               UNION

               SELECT -999999 WHERE NOT EXISTS (Select pub_id from publishers where pub_id = -1))

   print 'All equal'

  else

   print 'not all equal'

  Regards,
  gova

• ron k

  SSCertifiable

  Points: 6103

  More actions

  May 27, 2005 at 2:49 pm

  #561595

  I found this in the Database Developer's Companion (books NOT online):

  If a subquery introduced with ALL and a comparison operator does not return any values, all rows in outer query satisfy the condition.

  I think this is one feature I will not be using!