There are times when we

need to know that rows of data that meet specific criteria exist in a table. 

There are 2 basic ways to find this information:  COUNT() and EXISTS().  Here

are some examples.

In the Northwind database,

we want to enter orders for a particular customer.  A business rule says that if

this customer has placed an order before, they get a 10% discount.  To determine

this, we need to find out if there are rows in the Orders table for this

customer.

Example #1

In this example, we will count the number of

rows in the Orders table for the specified customer.  We will then check to see

if the this count is greater than zero. 

IF (SELECT COUNT(*)

FROM Orders WHERE CustomerID = 'GREAL') > 0

BEGIN

/*** PUT DISCOUNT

CODE HERE*/

                END

This technique works well, but an aggregate

function must be computed each time.  We can save this computation time by using

EXISTS().

Example #2

In this example, the

EXISTS() function returns a boolean value based on the subquery passed to the

function.  If rows exist, the function returns TRUE.

IF EXISTS(SELECT NULL FROM Orders WHERE CustomerID = 'GREAL')

BEGIN

/*

** PUT DISCOUNT CODE HERE

*/

END

You find that example #2 is

more efficient and helps with performance, especially when used on a table that

has a large amount of data.  To see the performance differences, use the

"Display Estimated Execution Plan" feature in Query Analyzer or set the

SHOWPLAN_ALL option ON before executing the queries.  These will show you the

extra steps needed to process the COUNT() aggregation.