The following list contains parts of filter conditions that you can specify:

• Table fields to compare

• Filter comparison operators, such as > or !=

• Filter options, such as ANY or LIKE, and values, such as field values, expressions, value sets, and subqueries

In addition to using the syntax of join conditions for one table to specify a filter, you can use comparison operators, such as =, <=, and >, to compare a field value to a filter value. The comparison operators you can use are the same as those available for comparison operators in join conditions.

The following table lists the comparison operators available for filter conditions.

The following example illustrates the use of the less than (<) operator in the filter condition, TAlias2.order_date < {^1994-02-16}, using the syntax, FieldName Comparison Expression, and displays only those company names in the Company field from the Customer table that have matching orders in the Orders table with order dates in the Order_Date field earlier than 02/16/1994.

	Copy Code
CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT TAlias1.company, TAlias2.order_date ; FROM customer TAlias1, orders TAlias2 ; WHERE TAlias1.cust_id = TAlias2.cust_id ; AND TAlias2.order_date < {^1994-02-16}

You can use filter options in addition to comparison operators so you can customize your filter condition to choose subsets of records. When using these options, the syntax of the filter condition can be one of the following:

• FieldName Comparison Expression | ALL (Subquery) | ANY (Subquery) | SOME (Subquery)

  -OR-

• FieldName [NOT] LIKE cExpression | IS [NOT] NULL | [NOT] BETWEEN Start_Range AND End_Range | [NOT] IN Value_Set | [NOT] IN (Subquery)

  -OR-

• [NOT] EXISTS (Subquery)

The following table describes options available for filter conditions.

Option	Description
ALL (Subquery)	FieldName values must meet comparison for all values generated by the subquery for inclusion in the query results.
ANY (Subquery) | SOME (Subquery)	FieldName values must meet comparison for at least one of the values generated by the subquery for inclusion in the query results.
LIKE "cExpression"	FieldName values must match the character expression cExpression, which can contain SQL wildcard characters, such as percent (%) and underscore (_), for inclusion in the query results. The % character represents any sequence of unknown characters in the string. The _ character represents a single unknown character in the string. If you want to specify a wildcard character as a literal, use the ESCAPE clause. For more information, see Wildcard Characters in Filter Conditions below. The LIKE "cExpression%" filter condition is fully optimized.
IS NULL	FieldName value must be null for inclusion in the query results.
BETWEEN Start_Range AND End_Range	FieldName values must be within a specified range of values for inclusion in the query results.
IN (Value_Set)	FieldName must contain one of the values or expressions specified in Value_Set for inclusion in the query results. When listing items in the value set, separate each item with commas. Note The number of values or expressions you can specify for Value_Set is affected by the setting of SYS(3055) - FOR and WHERE Clause Complexity. Tip Visual FoxPro stops evaluating values and expressions in the Value_Set list when the first match is found. Therefore, if the IN clause is not Rushmore optimized, you can improve performance by placing values most likely to match in the beginning of the Value_Set list.
IN (Subquery)	FieldName must contain one of the values returned by Subquery for inclusion in the query results.
EXISTS (Subquery)	Query contains results only when at least one row is returned from the subquery. The EXISTS clause evaluates to True (.T.) or False (.F.). Note The EXISTS (Subquery) clause evaluates to True (.T.) unless the subquery evaluates to the empty set.

The following small code examples illustrate various ways of specifying filter options and values.

• The following filter condition uses the syntax FieldName Comparison ALL (Subquery) and includes only those values that are less than all values generated by the subquery. Specifically, this condition specifies that the query include only those company names that come alphabetically before those whose country field contains "UK" in the Customer table.

  	Copy Code
  company < ALL (SELECT company FROM customer WHERE country = "UK")

• The following filter condition uses the syntax FieldName LIKE cExpression and includes only those values that match cExpression. Specifically, this condition specifies that the query include only those records where the Country field in the Customer table contains values matching "UK".

  	Copy Code
  customer.country LIKE "UK"

• The following filter condition uses the syntax FieldName BETWEEN Start_Range AND End_Range and includes only those values within the specified range of values. Specifically, this condition specifies that the query include only those postal codes in the Customer table that fall between "90000" and "99999."

  	Copy Code
  customer.postalcode BETWEEN "90000" AND "99999"

• The following filter condition uses the syntax FieldName IN (Value_Set) and includes only those values that are one of those specified in Value_Set. Specifically, this condition specifies that the query include only those postal codes in the Customer table that are "98052", "98072", and "98034."

  	Copy Code
  customer.postalcode IN ("98052","98072","98034")

• The following filter condition uses the syntax FieldName IN (Subquery) and includes only those values that are among those returned by the subquery. Specifically, this condition specifies that the query include only those customer ID numbers in the Customer table that are in the set of customer ID numbers in the Orders table that match orders whose City field contains "Seattle".

  	Copy Code
  customer.cust_id IN ; (SELECT orders.cust_id FROM orders WHERE orders.city="Seattle")

• The following filter condition uses the syntax, EXISTS (Subquery), and includes results only when at least one row is returned from the subquery. Specifically, this condition specifies that the query contain results only when at least one record is returned from the subquery. The subquery specifies to select all records from the Orders table in which the postal code in the Customer table matches the postal code in the Orders table.

  	Copy Code
  EXISTS (SELECT * FROM orders ; WHERE customer.postalcode = orders.postalcode)

The following complete example uses the EXISTS (Subquery) clause to create a query that contains results only when at least one record is returned from the subquery. The subquery specifies to retrieve company names from the Customer table only if at least one record in which the postal code in the Customer table matches the postal code in the Orders table.

	Copy Code
CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT company FROM customer TAlias1 ; WHERE EXISTS (SELECT * FROM orders TAlias2 ; WHERE TAlias1.postalcode = TAlias2.postalcode)

You can perform searches using wildcard characters in filter condition expressions in the WHERE clause of a SQL SELECT statement by using the percent (%) and underscore (_) characters. The % character represents any sequence of unknown characters in the string. The _ character represents a single unknown character in the string.

However, if you want to specify a filter condition expression that retrieves data containing wildcard characters, use the ESCAPE clause to treat the wildcard characters as literal characters. In the ESCAPE clause, you can specify a character that, when placed immediately before the wildcard character, indicates that the wildcard character be treated as a literal character.

The following examples illustrate how to perform searches using wildcard characters.

• The following example uses the LIKE cExpression option to display all records meeting a filter condition using the WHERE FilterCondition clause. The example creates a query that displays all records from the Customer table with a company name that begins with an uppercase letter C and is of unknown length by specifying the percent (%) wildcard character.

  	Copy Code
  CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT * FROM customer WHERE company LIKE "C%"

• The following example uses the LIKE cExpression option to display all records meeting a filter condition using the WHERE FilterCondition clause. The example creates a query that display all records from the Customer table with a country name that begins with an uppercase letter U and is followed by one unknown character by specifying the underscore (_) wildcard character.

  	Copy Code
  CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT * FROM customer WHERE country LIKE "U_"

The following examples show how to create queries on data containing wildcard characters, such as percent (%) and underscore (_).

• The following example uses the ESCAPE clause to specify that the backslash (\) is the escape character. The backslash is inserted immediately preceding the percent character to indicate that it should be treated as a literal.

  Note
  The sample tables included with Visual FoxPro do not contain the percent character; therefore, this example returns no results.

  	Copy Code
  CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT * FROM customer WHERE company LIKE "10\%" ESCAPE "\"

• The following example uses the ESCAPE clause to specify that the dash (-) character is the escape character. However, in the example, the dash character is specified as an escape character and a literal character. The example returns all records where the company name begins with a dash character.

  Note
  The sample tables included with Visual FoxPro do not contain the dash character in the company name; therefore, this example returns no results.

  	Copy Code
  CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT * FROM customer WHERE company LIKE "--" ESCAPE "-"

Tasks

Reference

Other Resources