Filter Conditions for Queries and Views
In SQL SELECT statements, filter conditions specify filter criteria that records retrieved from a SQL SELECT statement must meet to be included in query or view results. When creating queries and views with SQL SELECT statements to retrieve data, you can use filter conditions in the ON and WHERE clauses to extract only those records that meet the filter conditions. For example, you can use filter conditions to retrieve only those records where a field's values are less than a filter value, such as a date.
You can specify filter conditions similar to specifying join conditions but using filter values from a single table and customize filter conditions using comparison operators to control how those field values compare to those filter values. Join conditions compare a field value in one table to a field value in another other table instead of a filter value in the same table.
For the syntax of filter conditions in SQL SELECT statements, see SELECT - SQL Command.
This topic contains the following sections:
Parts of Filter Conditions
Filter Comparison Operators
Filter Options and Values
Wildcard Characters in Filter Conditions
Parts of Filter Conditions
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
Filter Comparison Operators
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.
Operator |
Description |
|---|---|
= |
Equal |
== |
Exactly equal |
LIKE |
SQL LIKE |
<>, !=, # |
Not equal |
> |
Greater than |
>= |
Greater than or equal to |
< |
Less than |
<= |
Less than or equal to |
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.
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}
Filter Options and Values
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.
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".
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."
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."
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".
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.
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.
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)
Wildcard Characters in Filter Conditions
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.
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.
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.
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.
CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + 'Data\TestData') SELECT * FROM customer WHERE company LIKE "--" ESCAPE "-"
See Also
Tasks
How to: Optimize Filters and Joins