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 Products WHERE 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 Products WHERE 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 Products WHERE ProductName IN ("Chair", "Table")

You can also use set notation for SQL DELETEs. 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 DELETEs. 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!

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article