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.
