Usage

Specifies which records from the tables listed in the FROM clause are affected by a SELECT statement.

Syntax

SELECT fieldlist

FROM tableexpression

WHERE criteria

A SELECT statement containing a WHERE clause has these parts:

Part

Description

fieldlist

The name of the field or fields to be retrieved from a database

tableexpression

The name of the table or tables from which data is retrieved.

criteria

An expression that records must satisfy to be included in the query results.

Remarks

The Alchemy database engine selects the records that meet the conditions listed in the WHERE clause. If you do not specify a WHERE clause, your query returns all rows from the table. If you specify more than one table in your query and you have not included a WHERE clause your query generates a Cartesian product of the tables.

WHERE is optional, but when included, follows FROM. For example, you can select all employees in the sales department (WHERE Dept = 'Sales') or all customers between the ages of 18 and 30 (WHERE Age Between 18 And 30).

Use various expressions to determine which records the SQL statement returns. For example, the following SQL statement selects all employees whose salaries are more than $21,000:

SELECT LastName, Salary

FROM Employees

WHERE Salary > 21000;

A WHERE clause can contain up to 40 expressions linked by logical operators, such as And and Or.

When you enter a field name that contains a space or punctuation, surround the name with brackets ([ ]). For example, a customer information table might include information about specific customers :

SELECT [Customer’s Favorite Restaurant]

When you specify the criteria argument, date literals must be in U.S. format, even if you are not using a language version of Windows on your desktop. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States. Be sure to enclose your date literals with the number sign (#) as shown in the following examples.

To find records dated May 10, 1996 in a United Kingdom database, you must use the following SQL statement:

SELECT *

FROM Orders

WHERE ShippedDate = #5/10/96#;

You can also use the DateValue function which is aware of the international settings established by Microsoft Windows®. For example, use this code for the United States:

SELECT *

FROM Orders

WHERE ShippedDate = DateValue('5/10/96');

And use this code for the United Kingdom:

SELECT *

FROM Orders

WHERE ShippedDate = DateValue('10/5/96');