MS-Access / Getting Started

---

WHERE Clause

Specifies a search condition in an SQL statement or an SQL clause. The DELETE, SELECT, and UPDATE statements and the subquery containing the WHERE clause operate only on those rows that satisfy the condition.

Syntax

WHERE <search-condition>

Notes The database applies the <search-condition> to each row of the logical table assembled as a result of executing the previous clauses, and it rejects those rows for which the <searchcondition> does not evaluate to True. If you use a subquery within a predicate in the <search-condition> (often called an inner query), the database must execute the subquery before it evaluates the predicate.

In a subquery, if you refer to a table or a query that you also use in an outer FROM clause (often called a correlated subquery), the database must execute the subquery for each row being evaluated in the outer table. If you do not use a reference to an outer table in a subquery, the database must execute the subquery only once. A correlated subquery can also be expressed as a join, which generally executes more efficiently. If you include a predicate in the <search-condition> in the form

<expression> <comparison-operator> <subquery>

the database returns an error if the subquery returns no rows.

The order of evaluation of the logical operators used in the <search-condition> is NOT, AND, OR, XOR (exclusive OR), EQV (equivalence), and then IMP (implication). (SQL Server does not support the XOR, EQV, and IMP logical operators.) You can include additional parentheses to influence the order in which the database processes expressions.

Examples

In a desktop database, to find all products for which the unit price is greater than $100 and for which the category description number is equal to Multi-User or the product has a prerequisite, but not both, enter the following (qxmplXOR):

SELECT tblProducts.ProductID, tblProducts.ProductName,
  tblProducts.CategoryDescription, tblProducts.UnitPrice,
  tblProducts.PreRequisite
 FROM tblProducts
 WHERE tblProducts.UnitPrice>100
  AND ((tblProducts.CategoryDescription = "Multi-User")
  XOR (tblProducts.PreRequisite Is Not Null));

In a project file, to find all products for which the unit price is greater than $100 and for which the category description number is equal to Multi-User or the product has a prerequisite, but not both, enter the following:

SELECT tblProducts.ProductID, tblProducts.ProductName,
  tblProducts.CategoryDescription, tblProducts.UnitPrice,
  tblProducts.PreRequisite
 FROM tblProducts
 WHERE tblProducts.UnitPrice>100
  AND ((tblProducts.CategoryDescription = "Multi-User")
  OR (tblProducts.PreRequisite Is Not Null))
  AND NOT ((tblProducts.CategoryDescription = "Multi-User")
  AND (tblProducts.PreRequisite Is Not Null));