Transact-SQL Cookbook

Chapter 2. Sets

SQL, as a language, was developed around the concept of a set. You may remember studying sets in elementary school, or perhaps you studied set algebra in high school or college. While SQL statements such as SELECT, UPDATE, and DELETE can be used to work on one row of data at a time, the statements were designed to operate on sets of data, and you gain the best advantage when using them that way. In spite of all this, we commonly see programs that use SQL to manipulate data one row at a time rather than take advantage of the SQL’s powerful set-processing capabilities. We hope that, with this chapter, we can open your eyes to the power of set manipulation.

When you write SQL statements, try not to think in terms of procedures such as selecting a record, updating it, and then selecting another. Instead, think in terms of operating on a set of records all at once. If you’re used to procedural thinking, set thinking can take some getting used to. To help you along, this chapter presents some recipes that demonstrate the power of a set-oriented approach to programming with SQL.

The recipes in this chapter are organized to demonstrate different types of operations that can be performed on sets. You’ll see how to find common elements, summarize the data in a set, and find the element in a set that represents an extreme. The operations don’t necessarily conform to the mathematical definition of set operations. Rather, we extend those definitions and use algebraic terminology ...