Use a WHERE
clause to limit the number of records in the recordset. For example, you may want to include only those customers who earn more than $50,000 a year. Assume you have a column in your table called Earnings
that tells you how much each customer earns. Your SELECT
statement would read as follows:
SELECT YearBorn, DateLastPurchase FROM Customers WHERE Earnings > 50000
You specify one or more conditions in a WHERE
clause to filter out records in the database. The following sections describe ways to filter records with the WHERE
clause:
You can filter records in a database based on the equality of a parameters value with a record columns value.
Suppose you decide to let users search the database by department. The following logic is required to build the search results recordset:
You can express this logic with the following WHERE
clause:
WHERE ColumnName = ParameterValue
ParameterValue
is a SQL variable containing a search parameter. In a web application, the user typically supplies this parameter using an HTML form.
This database query could be expressed fully in SQL as follows:
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, EMAIL FROM EMPLOYEES WHERE DEPARTMENT = 'varDept'
This SQL statement finds all the records in the employee table with a DEPARTMENT value equal to the value contained in the varDept
variable. For example, if the user specifies Operations as the department name, the SQL statement might generate the following recordset:
You can filter records in a database based on the likeness of a parameters value with a record columns value.
Using likeness instead of equality gives users more flexibility when specifying the value of search parameters. For example, search words dont need to be case sensitive. If the user enters ohio and the table column contains the value Ohio, the match is made.
Also, likeness lets you use wildcard characters so users can perform alphabetical and partial-word searches. For example, if the user enters m and the table column contains the values Morgan, Macy, and Michelson, then you can use a wildcard character in the SQL statement so that all three matches are made.
The standard wildcard character is the percentage sign (%):
...WHERE LastName LIKE 'Mc%'
Suppose you decide to let users search the database by last names. The following logic is required to build the search results recordset:
You can express this logic with the following WHERE
clause:
WHERE ColumnName LIKE ParameterValue
ParameterValue
is a SQL variable containing a search parameter. In a web application, the user typically supplies this parameter using an HTML form.
This database query could be expressed fully in SQL as follows:
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, EMAIL FROM EMPLOYEES WHERE DEPARTMENT LIKE 'varLastName'
If you want to give users the ability to perform partial-word searches, combine the variable with a wildcard character. The SQL wildcard character to use in this case is the percentage sign (%). Heres an example:
...WHERE LASTNAME LIKE 'varLastName%'
For example, if the user types s as the search parameter, all records with last names starting with the letter s are included in the recordset, as in the following example:
If the user specifies sm as the search parameter, then only those records with last names that start with the letters sm are included in the recordset:
You can filter records in a database based on whether a record columns value falls within the range of two parameter values.
Suppose you decide to let users search the database by a date range. The following logic is required to build the search results recordset:
You can express this logic with the following WHERE
clause:
WHERE ColumnName BETWEEN ParameterValue1 AND ParameterValue2
ParameterValue1
and ParameterValue2
are SQL variables containing search parameters. In a web application, the user typically supplies these parameters using an HTML form.
Heres how this type of database query can be expressed in SQL:
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, STARTDATE FROM EMPLOYEES WHERE STARTDATE BETWEEN #varStartRange# AND #varEndRange#
For example, if the user enters 7/1/99 and 12/31/99 as the range parameters, all employees starting in the second half of 1999 are included in the recordset, as in the following example:
This section describe how to include records in the search results recordset based on a combination of search conditions. You combine search conditions in SQL using the AND
, OR
, and NOT
logical operators.
If you want all the conditions to be true for a record to be included in the recordset, use the AND
operator as follows:
...WHERE LASTNAME LIKE 'varLastName' AND DEPARTMENT LIKE 'varDept'
If you want any one of the conditions to be true for a record to be included in the recordset, use the OR
operator as follows:
...WHERE LASTNAME LIKE 'varLastName' OR DEPARTMENT LIKE 'varDept'
If you want one condition to be true but not another, use the NOT
operator as follows:
...WHERE DEPARTMENT LIKE 'varDept' AND NOT COUNTRY LIKE 'varCountry'
You can use parentheses to group search conditions:
...WHERE (DEPARTMENT LIKE 'varDept' AND STARTDATE < #varStart#) OR STARTDATE BETWEEN #varStartRange# AND #varEndRange#