The WHERE Clause

You use the WHERE clause to restrict the records returned by your query to those matching your criteria specified by the WHERE clause. The WHERE clause evaluates each record in the set of input records and decides whether the expression contained in the WHERE clause evaluates to True. If it does, the record is selected. If not, the record is omitted from the query’s result set.

While it’s beyond the scope of this chapter to give a complete tutorial on SQL, the following representative examples point out some of the unique features of the Microsoft Jet version of SQL.

See Also For more information about SQL syntax, see Appendix B, “SQL Reference.”

Selecting Matching Values
SELECT Products.* 
FROM Products
WHERE Products.CategoryID = 2;

For each record in the Products table where the CategoryID field is equal to the literal value 2, the record is selected.

Selecting by Using Partial String Matching
SELECT Products.* 
FROM Products
WHERE Products.ProductName Like 'CH*';

Microsoft Jet uses partial match (or “wildcard”) characters with the Like operator that are different from those used in most SQL dialects. The asterisk (*) character matches zero or more characters and is equivalent to the percent (%) character in ANSI SQL. The other Microsoft Jet partial match characters are the question mark (?), which matches any character in a single field, and the number sign (#), which matches any digit in a single field.

Some SQL dialects require you to enclose text literals within single quotation marks. However, Microsoft Jet accepts literals enclosed in either single or double quotation marks.

Selecting Boolean Values
SELECT Products.* 
FROM Products
WHERE Products.Discontinued = True;

Microsoft Jet stores Boolean values as either  – 1 or 0. The constant value True is equal to  – 1; False is equal to 0. You can substitute Yes for True and No for False within the text of the SQL statement. Note, however, that if you refer to Boolean recordset values within your VBA code, you must use the values  – 1 for True and 0 for False, because Yes and No aren’t recognized there.

Selecting by Using Date Literals and the Between...And Operator
SELECT Orders.* 
FROM Orders
WHERE Orders.OrderDate Between #3/1/96# And #6/30/96#;

The convention Microsoft Jet uses to search for DATETIME values is to enclose literal search values in number signs (#). The literal either can include the date only, as in the following example, or it can be fully qualified with the date and time:

SELECT Orders.* 
FROM Orders 
WHERE Orders.OrderDate > #5/24/96#;

Note that this date literal must always be expressed in MM/DD/YY order. To avoid the ambiguity of the meaning of stored queries, Microsoft Jet doesn’t follow the international date format settings specified in the user’s Control Panel.

Selecting by Using the IN Clause and a Value List
SELECT Orders.* 
FROM Orders
WHERE Orders.ShipCity IN ('London','Madrid','Rome');

The previous code selects a single value in the underlying table from a list of literal values. The IN clause can be combined with NOT:

...WHERE Orders.ShipVia NOT IN (1,3);
Combining More Than One Expression
SELECT Orders.* 
FROM Orders
WHERE Orders.OrderDate > #3/1/96# AND Orders.ShipVia = 2;

A WHERE clause can consist of more than one Sub procedure that combines various AND and OR clauses. The truth of the entire WHERE clause is evaluated by using standard rules of Boolean logic. You can use parentheses with the Sub procedure to ensure the order of evaluation. Note, however, that the absence or presence of parentheses doesn’t affect the order in which joins are performed; parentheses affect only the order in which Boolean expressions are evaluated.

Selecting Unique Values
SELECT DISTINCT Employees.Title
FROM Employees;

Although not related to the WHERE clause, you can use the DISTINCT predicate of the SELECT clause to limit output records to specific unique combinations of output fields. In the previous example, one occurrence of each unique Title field value used in the Employees table is retrieved. If the output contains more than one field, only records unique across all selected fields are output, as in the following example:

SELECT DISTINCT 
	Employees.Title, 
	Employees.TitleOfCourtesy
FROM Employees;
Selecting Unique Records

The DISTINCTROW predicate omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order. DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query.

Note DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

Microsoft Access Users The DISTINCTROW predicate is the SQL equivalent of setting the Microsoft Access UniqueRecords property in a query to True. In Microsoft Access 97, the default setting for the UniqueRecords property in a query is False, which eliminates DISTINCTROW from the query’s SQL statement; in prior versions of Microsoft Access, the default setting for the UniqueRecords property is True, which adds DISTINCTROW to the query’s SQL statement. Three types of queries are affected by this change: UPDATE queries, DELETE queries, and SELECT queries.

Selecting by Using Nested Subqueries
SELECT Orders.* 
FROM Orders
WHERE Orders.CustomerID IN 
(SELECT CustomerID FROM Customers WHERE City = 'London');

This example matches each CustomerID value in the Orders table against a list that’s created by selecting the CustomerID values of all customers whose city is London. Although this is a standard SQL query, and legal in Microsoft Jet SQL, this particular search may be executed more efficiently by directly joining the Orders table to the Customers table.

Selecting by Using Correlated Subqueries
SELECT 
	T1.LastName, 
	T1.FirstName, 
	T1.Title, 
	T1.Salary
FROM Employees AS T1
WHERE T1.Salary >=
	(SELECT Avg(Salary) 
	FROM Employees 
	WHERE Employees.Title = T1.Title)
ORDER BY T1.Title;

A correlated subquery is evaluated once for each record processed by the main SELECT query. In this example, the Salary field of each input record in the Employees table (here given the alias name T1) is compared to the results of a sub-SELECT query. The sub-SELECT query uses the value in the main Title field as a selection criterion. Because the Title field in the main Employees table can change for each record processed, the sub-SELECT query must be re-executed for each record of the main table that’s processed. For example, if there are three employees with the title of Sales Representative, and the average of their three salaries is equal to $100,000 dollars, the main query will return only employees whose salary is greater than or equal to $100,000.

In addition to the features shown in the previous examples, Microsoft Jet supports a variety of other predicates for subqueries, including EXISTS, ANY, and ALL.