The next time you are writing your own queries, remember SQL Server's EXISTS construct. It is typically fast and easy to use, and it can prove very useful in programming situations.
One thing I have noticed over the years is how seldom developers use SQL Server's very useful EXISTS construct. You can use EXISTS in a variety of situations -- some of which I will explore in this article.
EXISTS
The EXISTS construct is a TSQL function that returns a Boolean value based upon the subquery inside of the parentheses. If the subquery returns any rows, the construct returns true; otherwise, it will return false. This construct is great for setting true or false variables that depend on the existence of records in a table. It is also good for determining whether to run an update or insert statement against tables (I'll show you an example of this later in the article).
Listing A creates a table and sample data that you can use to examine how the EXISTS construct works. The script in Listing B uses the EXISTS construct to check for rows in the SalesHistory table and populates a variable based on the existence of rows. This script will return the value 1 (true) to indicate that the SalesHistory table does contain rows. In this situation, the EXISTS construct will short circuit and return the value 1 immediately after it has found a record in the SalesHistory table. This is an added benefit to using the EXISTS construct -- it will return as soon as one row condition is met.
Listing C is similar to the script above, but it will return the value 0 because the condition inside of EXISTS is not met.
Because of the way in which the EXISTS construct works, the following statements are functionally equivalent.
SELECT * FROM SalesHistory
WHERE EXISTS(SELECT NULL)
SELECT * FROM SalesHistory
WHERE 1 = 1
SELECT * FROM SalesHistory
WHERE EXISTS(SELECT 1)
In the previous script, you might find this statement confusing:
SELECT * FROM SalesHistory
WHERE EXISTS(SELECT NULL)
Because NULL is a value in the database (it's an unknown value), the EXISTS construct will still return true in the presence of NULL.
Developers seldomly use the EXISTS operator with subqueries. The following queries will return the same resultset even though they are constructed differently. The first query uses EXISTS with a correlated subquery, while the second query compares the SalePrice in the SalesHistory table to the total average SalePrice from the SalesHistory table.
SELECT * FROM SalesHistory sh
WHERE EXISTS
(
SELECT AVG(SalePrice)
FROM SalesHistory shi
HAVING AVG(SalePrice) > sh.SalePrice
)
SELECT * FROM SalesHistory sh
WHERE SalePrice <
(
SELECT AVG(SalePrice)
FROM SalesHistory shi
)
As mentioned earlier, you can also use the EXISTS operator to determine if an UPDATE or an INSERT statement should be used with a single record, as the following example illustrates.
IF EXISTS
(
SELECT * FROM SalesHistory
WHERE SaleDate = '1/1/2001' AND
Product = 'Computer' AND
SalePrice = 1000
)
UPDATE TOP(1) SalesHistory
SET SalePrice = 1100
WHERE SaleDate = '1/1/2001' AND
Product = 'Computer' AND
SalePrice = 1000
ELSE
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Computer', '1/1/2001', 1100)
Although you probably wouldn't use the previous example in a production environment, it illustrates that you can easily use the EXISTS construct to determine which type of DML statement to use.
Give it a try
The next time you are writing your own queries, remember the EXISTS construct. It is typically fast and easy to use, and it can prove very useful in programming situations.
Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.
