When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Thursday, September 09, 1999

Using Sets with SQL Server


More Information on Set Notation
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 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!


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