Network Computingwww.networkcomputing.com

When looking for NULL values, we must use the IS keyword. As our second example, above, showed, NULL does not equal anything, including other NULLs. We cannot run a calculation against a NULL, nor can we perform standard comparisons. This is the only context in which we will encounter the IS operator, since its only purpose is to look for NULL values.

We have seen how the NULL operator is a special type. We can use the IS NULL operator to test for NULL values, but you cannot use the = operator on NULL fields.

Calculating in WHERE Clauses

The search criteria in a WHERE clause may contain calculations on fields. For example, we can find all of the products that we have more than $3000 of in inventory with this statement:

SELECT ProductName, UnitsInStock * UnitPrice
FROM Products
WHERE UnitsInStock * UnitPrice > 3000

Click here to enlarge

Using the WHERE clause to filter calculated values will provide a lot of power to our queries. One of the more powerful aspects of SQL is its ability to calculate values either on a row-by-row basis or on an entire table. For example, we could use the WHERE clause to filter each customer order or we could filter based on the average customer order (after requesting a table-wide calculation to find the average customer order). We will look at calculating values in more detail in later sections.

Try it Out - Filtering Based on Calculated Values

We would like to know the orders where a customer bought more than $10,000 worth of an individual item.

3. The details for the order are stored in the Order Details table. Notice that there is a space in the name of the table. When we refer to this, we will need to use square brackets to enclose the table name or we will get an error. Remember to use quotation marks if you are using a different vendor product.

3. Note that we can calculate the total amount of money spent by a customer on a single order by simply multiplying the Quantity and the UnitPrice fields.

4. We can therefore construct a query that involves the multiplication of two columns where the result of this multiplication is larger that $10,000. Construct and enter the following query:

SELECT OrderID, UnitPrice * Quantity
FROM [Order Details]
WHERE UnitPrice * Quantity > 10000

SELECT OrderID, UnitPrice * Quantity AS LineItemTotal
FROM [Order Details]
WHERE UnitPrice * Quantity > 10000

When this query executes, a calculation is performed on each row, generating the total for each line item. Once the new row is generated, then it is evaluated to see if it's greater than 10,000. If it is, then it is added to the result set.

Remember that these calculations are performed on each row of data. We should exercise caution when using very complex calculations as they can run very slowly if there is a large amount of data. If we want to run the same calculations on data that will not change often (such as purchase data) then it may be to our advantage to create an additional table to hold these calculated values. We will look at how to create tables in Chapter 6.

Let's look at another table of examples, this time of filtering based on calculated values, and how such requests translate into SQL.

Common Errors

There is really only one common "error" that occurs with this type of query. More often than not, the problem will be that incorrect results are returned. This is usually due to a logic error or some calculation error. Double check your logic within the statement to be sure that you are actually filtering on the correct data.

Working with Ranges

The BETWEEN operator allows us to search for a range of values in a WHERE clause. It provides a means of restricting the scope of the WHERE clause. For instance, we could use the BETWEEN operator to look for all part numbers between the values of 100 and 200.

The syntax for the BETWEEN operator is as follows:

BETWEEN <Start Value> AND <End Value>

The start and end values in a BETWEEN statement are inclusive.

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '7/1/1996' AND '7/15/1996'

Again, remember that if you're using Oracle, you must write the dates as 1-JULY-1996 and 15-JULY-1996 as Oracle will not accept the American standard format.

Now, the BETWEEN operator is really nothing special. We could write our query another way to achieve the same results. We could simply use our standard >= and <= operators in the following way:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate >= '7/1/1996' AND
OrderDate <= '7/15/1996'

This is a good example of where the SQL language provides many ways to get to the same data. We will be seeing more examples of this as we progress.