To read the article online, visit http://www.4GuysFromRolla.com/webtech/090999-1.shtml

Using Sets with SQL Server

For more information on using SQL Set notation be sure to check out:

Did you know that SQL allows set-like operations? Using sets, you can quickly delete a set of rows with one SQL statement, or update a set of rows using a single statement. Heck, you can even query data against a set of acceptable parameters! Sets are really cool things, and SQL let's you take advantage of the power of sets!

You may be scratching your head, trying to remember what, exactly, sets are. We've all likely had a high school math course that dealt with fundamental set theory, but that was ages ago! :) Here are some set basics to help stir your memory.

• Sets are an unordered collection of items. That means that sets `{1, 3, 2, 5}` and `{5, 2, 3, 1}` are identicle sets.

• If we have the set `{1, 2, 3}`, we say that the set has a cardnality of three. The cardnality refers to the number of elements in the set (which gets a tad difficult once you start dealing with sets with an infinite number of elements, but that is a bit beyond this article!). Also, we say that 1 is an element in the set `{1, 2, 3}`, as are 2 and 3.

That's really all the refreshing you'll need. SQL doesn't allow any complex operations on sets. Rather SQL uses set notation to determine if a given value in a table is an element of a given set. That is probably as clear as mud. Let's look at an example. We've probably all done SQL queries. Let's construct a SQL query to search for all items in a `Products` table that cost either 5, 6, 7, 8, 9, or 10 dollars.

 ``` SELECT * FROM ProductsWHERE Cost = 5 OR Cost = 6 OR Cost = 7 OR Cost = 8 OR Cost = 9 OR Cost = 10 ```

Using set notation, we can write that query with a bit more elegance. SQL uses the parenthesis to denote the start and stop of a set. For example, the set `{1, 2, 3}` would be denoted as `(1, 2, 3)` in SQL. Now let's look at that revised query!

 ``` SELECT * FROM ProductsWHERE Cost IN (5, 6, 7, 8, 9, 10) ```

Here we are saying, "Get us all the records from the Products table where the cost of the product is in the set `(5, 6, 7, 8, 9, 10)`. Pretty neat, eh? You can also use sets for string data. Say that you had a column in the `Products` table named `ProductName`. If you wanted to return all of the products that were named Chair or Table, you could use sets like so:

Using set notation, we can write that query with a bit more elegance. SQL uses the parenthesis to denote the start and stop of a set. For example, the set `{1, 2, 3}` would be denoted as `(1, 2, 3)` in SQL. Now let's look at that revised query!

 ``` SELECT * FROM ProductsWHERE ProductName IN ("Chair", "Table") ```

You can also use set notation for SQL `DELETE`s. Say that you wanted to delete all rows that had an ID of 5, 6, or 7. Sure, you could issue three SQL statements, or you could just use set notation!

 ``` DELETE FROM TableName WHERE ID IN (5, 6, 7) ```

What if you wanted to write a SQL statement that would delete all of the records in a table, except those with an ID of 5, 6, or 7? You can use set operators still! Instead of saying, `WHERE ColumnName IN ...`, write, `WHERE ColumName NOT IN ...`. Here is an example:

You can also use set notation for SQL `DELETE`s. Say that you wanted to delete all rows that had an ID of 5, 6, or 7. Sure, you could issue three SQL statements, or you could just use set notation!

 ``` DELETE FROM TableName WHERE ID NOT IN (5, 6, 7) ```

The above query will delete any rows from the table that do not have an ID of 5, 6, or 7.

The coolest thing about sets, in my opinion, is that you can generate a set in one of two ways. Through hard coding it, like in any of our above queries, or, more powerfully, through a separate `SELECT` statement! Imagine that you have two tables, `Products`, which has a complete listing of all your products for sale, and `ProductSpecials`, a table that lists the current specials. `ProductSpecials` has a foreign key to the `Products` table so that it can associate the information of a certain special with a particular product. Your `Products` table contains a column called `Inventory`, which is the current number of items in stock of the particular product. If the inventory for a particular item falls to zero, you want to delete it from the `ProductSpecials` table if an entry for that product exists. An ugly query? Nah! Sets to the rescue!

 ``` DELETE FROM ProductSpecials WHERE ProductID IN (SELECT ID FROM Products WHERE Inventory = 0) ```

What does this code do? Well, first look at the `SELECT` statement. It returns a set of product IDs for products that contain zero inventory. Say that there are three such ID's: 1, 7, and 19. Now, let's say there are currently four specials going on. One for product ID 3, one for product ID 8, one for product ID 19, and one for product ID 22. This query will remove any rows from `ProductSpecials` whose ProductID is in the set of products with zero inventory. In the example numbers we gave, the product special for product ID 19 would be deleted from the `ProductSpecials` table.

Hopefully you are thinking, "Sets are freakin' cool." I suspect, though, that many folks out there are thinking, "Yeah, it's neat and all, but I don't see why it's so useful." Well, I will tell you why: it makes batch deletes or batch updates through ASP pages very easy! I will demonstrate how to accomplish this in my next article! Heck, I just might talk more about set theory (a topic that interests me)! :)

For now, go ahead and play around with the set notation, construct some queries, delete some data.

Happy Programming!

•  Article Information Article Title: Using Sets with SQL Server Article Author: Scott Mitchell Published Date: Thursday, September 09, 1999 Article URL: http://www.4GuysFromRolla.com/webtech/090999-1.shtml