Exists Versus Count(*)

A glib suggestion is made in Optimizing SQL to "Use Exists instead of Count". The obvious question is "How?".

One of the most common uses of count(*) is to answer the question "Does this exist?" or "Are there any rows that satisfy this condition?" The count(*) function does more than just answer the question, it actually counts the number of rows that satisfy the condition:

long ll_count
select count(*)
  into :ll_count
  from xref_info
 where short_event like '%open%'
 using SQLCA;
if SQLCA.SQLCode <> 0 then
   MessageBox ( "Error", "Count failed" )
   return
end if

The Watcom if-then-else-endif construction can be used together with exists to return a simple yes/no answer. Timing tests show that the SQL optimizer does a pretty good job because the following select ran 20 times faster on one machine:

long ll_yes_no
select if exists (
          select short_event
            from xref_info
           where short_event like '%open%' )
       then 1
       else 0
       endif
  into :ll_yes_no
  from sys.dummy
 using SQLCA;
if SQLCA.SQLCode <> 0 then
   MessageBox ( "Error", "Exists failed" )
   return
end if