You can use a Where clause in a SQL statement to filter which records are
returned in the query's result set. The syntax of a SQL statement containing a Where
clause is this:
Select FieldList From TableName Where Condition
The Condition of the Where clause can be one or more comparison statements using
the usual comparison operators such as =, >, <, ³, and ..., as well as other operators
more specific to the SQL language such as Like (for text comparisons) and Between...And (for
specifying a range of values).
A Where clause that only returns records with a field matching a particular value
would use the = operator, as in the following example:
Select * From Employees Where Dependents = 0
A Where clause to return values above a certain value would use the > operator,
as in the following example:
Select * From Employees Where Salary > 40,000
You would use similar rules for the <, =, ..., and ³ operators. Note
that when quoting literal strings for comparison in a SQL Where clause, you use the single quotation
mark character, as in the following example:
Select * From Employees Where LastName = 'Smith'
When using a literal date value in a Where clause comparison, you must use the
U.S. date format (this format being mm/dd/yy)—even when you are not looking at data with a U.S.
date format. You must then set the date off with the # character. The following example
illustrates the use of a date value in a SQL Where clause:
Select * from Employees Where HireDate < #1/1/89#
You can use Between...And to specify a range of values to allow in the result
set, as in the following example, which would allow all employee records with salaries between
40,000 and 80,000, inclusive, into the result set:
Select * From Employees Where Salary BETWEEN 40000 AND 80000
To obtain a text match with a field that contains a certain string combination
(but does not exactly match the string), you can use the Like operator and specify "wildcard"
characters similar to UNIX, DOS, or Windows operating system "wildcard" characters
as used in file specifications.
Use the % character to specify any number of characters and the _ character
to specify a single character. The following example shows a query for all records with a
Last Name field beginning with the letter S:
Select * From Employees Where LastName Like 'S%'
String comparisons in Where clauses are not case sensitive.
NOTE - Other Types of Clauses: In addition to
the basic SQL structure, which specifies fields and a table, you can add a number
of different types of clauses to the statement. Only the Where, Order By, and
Join clauses are discussed in this chapter. You should be aware that other types
of clauses exist for SQL Select queries.