SELECT - SQL Command - WHERE Clause
The WHERE clause specifies join and filter conditions that determine the rows that the query returns. Join operations in the WHERE clause function the same as JOIN operations in the FROM clause.
Note
Including the EVALUATE( ) function in the WHERE clause of a SQL query can return incorrect data.
For the more information, see SELECT - SQL Command.
The detailed syntax for the WHERE clause is as follows:
[WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...]
Parameters
JoinCondition
Specifies conditions on which the tables in a SQL SELECT statement are joined. For multiple join conditions, you must use the AND or OR operator to connect those conditions. For more information about JoinCondition, see the ON clause in the FROM clause.FilterCondition
Specifies criteria that records must meet to be included in the query results. For more information about FilterCondition, see the ON clause in the FROM clause.
Remarks
JoinCondition or FilterCondition can be an IN clause. The IN clause is of the form IN (Value_Set) where Value_Set is Expr1[, Expr2[, ...[ ,ExprN]]]. In Visual FoxPro 9.0 the IN clause is evaluated in a different manner than previous versions; see Changes in Functionality for the Current Release for more information.
The following demonstrates the format of the IN clause.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor(Field1 I, Field2 I)
INSERT INTO MyCursor values(1,6)
INSERT INTO MyCursor values(2,5)
INSERT INTO MyCursor values(3,4)
SELECT * FROM MyCursor WHERE MyCursor. Field1 ;
IN (1,2,3,4,5)
The following code shows a summary of the main clauses of the SELECT - SQL Command:
SELECT Select_List
FROM Table_List
...[WITH (BUFFERING = lExpr)]
[WHERE Conditions]
[GROUP BY Column_List]
[UNION Clause]
[HAVING Conditions]
[ORDER BY Column_List]
[INTO Clause | TO Clause ]
[Additional_Display_Options]
For more information about a particular clause of the SQL SELECT command, see the following topics:
You can create filter conditions that search for data containing SQL SELECT wildcard characters, such as percent (%) and underscore (_), by using the ESCAPE clause in the WHERE clause. 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. For more information and examples, see Filter Conditions for Queries and Views.
Example
The following example displays three fields from two tables, but only those records meeting join and filter conditions in the WHERE clause. The example joins the Customer and Orders table on the Cust_ID field and displays the Company, Order_Date, and Shipped_On fields for only those records that have an order date earlier than 02/16/1994. The SELECT statement specifies local aliases for the tables to distinguish the same field name, Cust_ID, in both tables.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.company, TAlias2.order_date, TAlias2.shipped_on ;
FROM customer TAlias1, orders TAlias2 ;
WHERE TAlias1.cust_id = TAlias2.cust_id ;
AND TAlias2.order_date < {^1994-02-16}