|
|
|
The WHERE clause specifies filtering conditions for the SELECT statement. The syntax is as follows:
WHERE predicates
Use a WHERE clause to limit the effect of a SELECT statement to a subset of rows in the table. The value for a WHERE clause is one or more logical expressions, or predicates, that evaluate to true or false for each line in the table. Only those rows where the predicates evaluate to TRUE are retrieved by the SELECT statement. For example, the SELECT statement below retrieves all rows where the CountryCode field contains a value of NZ:
SELECT CustomerCode, CountryCode FROM ARCUST WHERE CountryCode = 'NZ'
A field 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 CountryCode field could be used in the WHERE clause even if it was not also in the SELECT clause.
You can use multiple predicates, 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 line evaluation criteria. For example, the SELECT statement below retrieves all rows where the CountryCode field contains a value of NZ and those with a value of AU:
SELECT CustomerCode, CountryCode FROM ARCUST WHERE (CountryCode = 'NZ') OR (CountryCode = 'AU')
The SELECT statement below retrieves all rows from ICPROD (IC Product) where the Style (Category1) field is RUSTIC or SHAKER, but only if the Range (Category2) field is BED.
SELECT ProductCode, Category1, Category2 FROM ICPROD WHERE ((Category1 = 'RUSTIC') OR (Category1 = 'SHAKER')) AND (Category2 = 'BED')
Parentheses affect the order or precedence of the logical operators. Without parentheses the above statement would return different results. The following statement retrieves rows where the Style is RUSTIC, regardless of the value of the Range field. It also retrieves rows where the Style is SHAKER but only when the range is BED.
SELECT ProductCode, Category1, Category2 FROM ICPROD WHERE Category1 = 'RUSTIC' OR Category1 = 'SHAKER' AND Category2 = 'BED'
Field correlation names cannot be used in filter comparisons in the WHERE clause. Use the field name.
A WHERE clause filters data prior to the aggregation of a GROUP BY clause. For filtering based on aggregated values, use a HAVING clause.
Fields devoid of data contain NULL values. To filter using such field values, use the IS NULL predicate.
You can search for reserved characters using the ESCAPE keyword with LIKE. For example, to search for a customer with a % character in the customer code:
SELECT ARCUST.CustomerCode, ARCUST.CustomerName FROM ARCUST WHERE ARCUST.CustomerCode LIKE '%!%%' ESCAPE '!'
This will return all customers with a % in the customer code.
|
|
|
