Partager via


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}

See Also

Reference

CREATE QUERY Command

CREATE TABLE - SQL Command

MODIFY QUERY Command

Other Resources

Working with Queries

Working with Views (Visual FoxPro)

Query and View Designers