Data Management

Put SQL Server's EXISTS construct to good use

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).

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

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.

About Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks

Free Newsletters, In your Inbox