|
Return to Newsletter Contents...
A Better Way EXISTS
by:
Sean Beal, Senior Consultant,
CRCP
Regularly, the COUNT(column) function is used to evaluate not only the
number of rows returned, but also to check whether any rows are returned
at all. As an example, consider the following (based on the Northwind
database):
if (select count(*) from orders where customerid = 'DRACD') > 0
print 'Drachenblut Delikatessen has orders'
If we care only that there is at least one row, a much better way to code
this is to use the EXISTS keyword:
if exists (select * from orders where customerid = 'DRACD')
print 'Drachenblut Delikatessen has orders'
The reason is this: the COUNT() function must read all rows that
fit our requirements; in this case, where the customer id is equal to ‘DRACD’.
The EXISTS function allows our IF statement to evaluate to true after finding
only one record.
Queries can in a similar way be optimized by using the positive EXISTS rather
than the negative NOT EXISTS where possible, because EXISTS evaluates to true
based on one record, whereas NOT EXISTS must evaluate all the records in order
to prove non-existence.
Go to Top |
Return to Newsletter Contents
|
