<< Click to Display Table of Contents >> WHERE clause |
Specifies filtering conditions for a SELECT or UPDATE statement.
WHERE predicates
Use a WHERE clause to limit the effect of a SELECT or UPDATE statement to a subset of rows in the table. In statements that support a WHERE clause, the clause is optional. (An exception where the WHERE clause is required is the equi-join).
The value for a WHERE clause is one or more logical expressions, or predicates, that evaluate to TRUE or FALSE for each row in the table. Only those rows where the predicates evaluate to TRUE are retrieved by a SELECT statement or modified by an UPDATE statement. For example, the SELECT statement below retrieves all rows where the STATE column contains a value of "CA".
SELECT Company, State
FROM Customer
WHERE State = "CA"
A column used in the WHERE clause of a statement is not required to also appear in the SELECT clause of that statement. In the preceding statement, the State column could be used in the WHERE clause even if it was not also in the SELECT clause.
Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different row evaluation criteria. For example, the SELECT statement below retrieves all rows where the STATE column contains a value of "CA" and those with a value of "HI".
SELECT Company, State
FROM Customer
WHERE (State = "CA") OR (State = "HI")
The SELECT statement below retrieves all rows where the SHAPE column is "round" or "square", but only if the the COLOR column also contains "red". It would not retrieve rows where, for example, the SHAPE is "round" and the COLOR "blue".
SELECT Shape, Color, Cost
FROM Objects
WHERE ((Shape = "round") OR (Shape = "square")) AND
(Color = "red")
But without the parentheses to override the order of precedence of the logical operators, as in the statement that follows, the results are very different. This statement retrieves the rows where the SHAPE is "round", regardless of the value in the COLOR column. It also retrieves rows where the SHAPE column is "square", but only when the COLOR column contains "red". Unlike the preceding variation of this statement, this one would retrieve rows where the SHAPE is "round" and the COLOR "blue".
SELECT Shape, Color, Cost
FROM Objects
WHERE Shape = "round" OR Shape = "square" AND
Color = "red"
Subqueries are supported in the WHERE clause. A subquery works like a search condition to restrict the number of rows returned by the outer, or "parent" query. Such subqueries must be valid SELECT statements. SELECT subqueries can be correlated by referring to columns in the outer (or "parent") statement. In the following statement, the subquery is said to be correlated because it refers to the C.Acct_Nbr column of the parent statement.
UPDATE "Clients.dbf" C
SET C.Interests = "Buys lotsa stock"
WHERE EXISTS
(SELECT H.Acct_Nbr, SUM(H.Shares)
FROM "Holdings.dbf" H
WHERE (H.Acct_Nbr = C.Acct_Nbr)
GROUP BY H.Acct_Nbr
HAVING (SUM(H.Shares) >= 50000))
Note: Column correlation names cannot be used in filter comparisons in the WHERE clause. Use the actual column name.
Note: A WHERE clause filters data prior to the aggregation of a GROUP BY clause. For filtering based on aggregated values, use a HAVING clause.
Hint: When filtering using most column types, the simple comparison predicates suffice. But when filtering based on the contents of a MEMO column, you must use the LIKE predicate.
Hint: Columns devoid of data contain NULL values. To filter using such column values, use the IS NULL predicate.
SELECT (filtering on non-aggregated columns) and UPDATE statements.