WHERE Clause
The WHERE Clause is one of the most powerful features of a query. It allows you to restrict the rows returned based one or more criteria. These criteria are in the form of an expression, the general form of which is:
[Field] <comparison operator> [Value]
The field is a column in the base table(s) or a calculated column. It does not have to be in the Field List. The comparison operators are the standard math comparators: =, <, >, <=, >=, or <>; with some SQL specific ones added: IN/EXISTS, BETWEEN, LIKE, and IS NULL. The value portion can be either a hard coded value (like "hammer" or 25), or it can be another field or expression.
The WHERE clause follows the From Clause in a SQL Statement like so:
SELECT ProductName, Cost, Price, [Price]-[Cost] AS Margin
FROM Products
WHERE ProductName="hammer"
Examples of simple WHERE clauses:
WHERE ProductName="hammer"
(to show all hammers)
WHERE ProductName <> "hammer"
(to show all products EXCEPT hammers )
WHERE Cost <= 0.05
WHERE Price < Cost
(products sold below cost)
WHERE [Price]-[Cost]>2*[Cost]
(where the margin is greater than twice the cost)
LIKE is used with character data only and uses the asterisk as a wildcard symbol:
WHERE ProductName LIKE "ham*"
(returns "hammer" and "hammock")
WHERE ProductName LIKE "*nail"
(returns "10p nail" and "8p nail")
IN allows you to test if a field matches one of a list of values.
WHERE Cost IN (1, 2, 5, 8)
IN can also contain another SQL statement. The SQL statement contained in the IN must have only one field in the Field List.
WHERE ProductName IN (SELECT ProductName FROM Products)
I'll talk more about this in a later post when I discuss subqueries.
BETWEEN allows you to test for a range of values:
WHERE Cost BETWEEN 5 AND 10
WHERE BeginDate BETWEEN #1/1/2008# AND #12/31/2008#
IS NULL is a special comparator that tests whether or not a field is Null. The other comparison operators do not work with Null, so IS NULL is the only way to test for it.
WHERE ProductName IS NULL
Multiple Criteria and Logical Operators
You can also have multiple criteria by using multiple expressions joined by the Logical Operators: AND/OR. Examples of simple multiple criteria:
WHERE ProductName="10p nail" OR ProductName="8p nail"
WHERE ProductName="saw" AND Cost BETWEEN 5 AND 10
WHERE ProductName="saw" OR Cost BETWEEN 5 AND 10
Unfortunately, the Query Builder View of WHERE clauses (or Criteria as it's called in the QB) looks quite different than in the SQL View. In the QB, you do not need to repeat the field name with an OR or AND statement like you do in SQL. For instance, if I wanted to display both 10p nails and 8p nails, my WHERE clause would look like this:
WHERE ProductName="10p nail" OR ProductName="8p nail"
But the Query builder would look like this:
Figure 1 
Of course, this would also work:
Figure 2 
You have to be careful when creating multiple criteria in the Query Builder because sometimes they don't say exactly what you think they do. It matters which lines in the Criteria that you put your expressions. You create ORs on separate lines, while you create ANDs on the same line.
So this:
WHERE ProductName="saw" OR Cost BETWEEN 5 AND 10
Translates to:
Figure 3
(Notice the criteria are on separate lines.)
But this:
WHERE ProductName="saw" AND Cost BETWEEN 5 AND 10
Translates to:
Figure 4 
(Notice here the criteria are on the same line.)
It gets even more complicated with multiple ANDs and Ors. For instance, what does this statement mean?
WHERE ProductName="saw" OR ProductName="hammer" AND Cost BETWEEN 5 AND 10
You might think it means I want "saws and hammers with a cost between 5 and 10". However, it doesn't. There is an Order of Precedence to the logical operators as there are with arithmetic symbols. AND always takes precedence over OR. So what will really be returned is "ALL saws and only those hammers that cost between 5 and 10". The AND expression will be evaluated before the OR expression.
So how do I get what I want? The answer is parentheses. If I want something evaluated out of precedence (like my OR expression) I surround it with parentheses. So it would be this:
WHERE (ProductName="saw" OR ProductName="hammer") AND Cost BETWEEN 5 AND 10
In the Query Builder, it would look like this:
Figure 5
One last thing about the Query Builder and WHERE clauses. The QB overdoes it with table names and parentheses. If I create the following query in the Query Builder:
Figure 6
I will get the following SQL statement:
SELECT Products.ProductName, Products.Price,
FROM Products
WHERE (((Products.ProductName)="saw"
Or (Products.ProductName)="hammer")
AND ((Products.Cost) In (1,2,5,7)));
Because there is only one table in the query, the table name preceding every field is not necessary. However, it also puts in parentheses, which, while it makes the query technically correct, actually makes it harder to read. Many of these parentheses can be removed, but not all. Naturally, the parentheses that surround the IN list must remain and also the OR statement that we know we want to take precedence. The query can be cleaned to look like this:
SELECT ProductName, Price
FROM Products
WHERE (ProductName="saw"
Or ProductName="hammer")
AND Cost In (1,2,5,7);
Because of the possibility of error when creating complex WHERE clauses in the Query Builder, I usually create them in the SQL View where I know I can control the parentheses.
NOT Operator
There is one more important Logical Operator, and that's the NOT operator. The NOT operator reverses an expression. It can be used with the LIKE, IN, and BETWEEN operators or entire multiple-expression WHERE clauses. It returns all rows EXCEPT those that would have been returned if you hadn't used the NOT.
WHERE ProductName NOT LIKE "ham*"
WHERE Cost NOT IN (1, 2, 5, 8)
WHERE Cost NOT BETWEEN 5 AND 10
WHERE Cost NOT IS NULL
It's important to remember that NOT also reverses both the comparison operators and the other logical operators, so you need to be really careful. For instance,
WHERE NOT(ProductName="saw" Or ProductName="hammer")
Is equivalent to:
WHERE (ProductName<>"saw" AND ProductName<>"hammer")
Next time, I'll look at sorting and groupin in: Select Queries Part 3: Sorting and Grouping (ORDER BY, GROUP BY).
.
No comments:
Post a Comment