WHERE clause

<< Click to Display Table of Contents >>

Navigation:  Local SQL > Data Manipulation Language > Clauses >

WHERE clause

Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

Specifies filtering conditions for a SELECT or UPDATE statement.

WHERE predicates

Description

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.

Applicability

SELECT (filtering on non-aggregated columns) and UPDATE statements.