| 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.
{1, 3, 2, 5} and
{5, 2, 3, 1} are identicle sets.
{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
|
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
|
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
|
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
|
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
|
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
|
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!
