Specifying Criteria with the WHERE Clause

Specifying Criteria with the WHERE Clause

A WHERE clause tells the database engine to limit the records it retrieves according to one or more criteria that you supply. A criterion is an expression that evaluates to a true or false condition; many of the same expressions of equivalence to which you're accustomed in Visual Basic (such as >0 and = 'Smith') exist in SQL as well.

For example, say that you want to return a list of only those customers who live in California. You might write an SQL query such as

 SELECT FirstName, LastName, State  FROM tblCustomer WHERE State = 'CA' 

This query retrieves the record for the customer who lives in California, Daisy Klein.

Note also that the delimiter for a text string in a WHERE clause is a single quotation mark. This marker is convenient, as you'll see later, because the delimiter for a string in VB.NET is a double quotation mark, and SQL statements must sometimes be embedded in VB code.

You can create more sophisticated WHERE clauses by linking two or more criteria with AND and OR logic. For example, say that you want to retrieve all the customers who live in Denver, Colorado (as opposed to those customers who live in other cities in Colorado). To do so, you need to denote two criteria linked with an AND operator:

 SELECT  FirstName, LastName, City, State  FROM  dbo.tblCustomer WHERE  (State = 'CO') AND (City = 'Denver') 

Hypothetically, running this query should retrieve Thurston Ryan, the customer who lives in Denver, Colorado. If you had more than one customer in Denver, Colorado, they'd all be retrieved by this query. However, it wouldn't retrieve any customers who live in a city named Denver in some state other than Colorado (assuming that such a place actually exists).

If you're interested in seeing information on people who live in two states for example, both Colorado and California use an OR clause to link the two criteria, as in

 SELECT  FirstName, LastName, City, State  FROM    tblCustomer WHERE  State='CO' OR State='CA' 

Running this query retrieves the three records from tblCustomer who live in California or Colorado. As these examples clearly show, you can go nuts trying to link WHERE criteria with AND and OR conditions to extract data from a table.

Note

One key to successful database development is to keep client applications from retrieving too many records at once. Doing so will ensure that your applications run quickly and won't do bad things such as causing users' computers to run out of memory. One of the most basic weapons that you can use to avoid these unfortunate results is the WHERE clause.

Operators in WHERE Clauses

You can use the operators listed in Table 2.1 to construct a WHERE clause. The equality and in equality operators work exactly the same way in SQL as they do in VB.NET.

Table 2.1. Operators for Use in WHERE Clauses

OperatorS	Function
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
=	Equal to
<>	Not equal to
BETWEEN	Within a range of values
LIKE	Matching a pattern
IN	Contained in a list of values

The BETWEEN Operator

The BETWEEN operator returns all a record's values between the limits you specify. For example, to return all the orders placed between January 4 and June 5, 2001, you would write the SQL statement

 SELECT *  FROM tblOrder WHERE OrderDate BETWEEN '1/4/2001' and '6/5/2001' 

This query produces the result set shown in Figure 2.4.

Figure 2.4. Query results obtained by running a SELECT against tblOrder, using the BETWEEN operator

Note that, as with strings, date parameters in SQL Server are delimited with single quotes. If you're accustomed to delimiting dates with pound signs (#), as in Microsoft Access, you'll have to adjust when using dates as parameters in SQL Server.

Note also that the boundaries of a BETWEEN operator are inclusive. That is, if you ask for all the orders placed between January 4 and June 5, as you're doing here, the result set will also include records placed on January 4 and June 5.

The LIKE Operator and Wildcard Characters

The LIKE operator matches records to a pattern you specify. This pattern is often a wildcard character, such as the (*) or (?) character with which you may be familiar from working with the MS-DOS or Windows file systems.

The percent (%) character indicates a partial match. For example, to retrieve all the records in tblCustomer whose last names begin with the letter J, you'd use a query such as

 SELECT ID, FirstName, LastName, Address, City, State  FROM tblCustomer WHERE FirstName LIKE 'J%' 

This query retrieves the three people in the customer table whose first names begin with the letter J.

You can also create wildcard matches by using the underscore character. It takes the place of a single character in a pattern. For example, to locate all the customers with five-digit zip codes beginning with the number 80, use the expression LIKE 80__, with three underscores to represent the three "wild" characters, as in

 SELECT ID, FirstName, LastName, Address, PostalCode  FROM tblCustomer WHERE PostalCode LIKE '80__' 

This query retrieves the two customers in the database who have postal codes beginning with 80.

You can also use a LIKE operator that returns a range of alphabetic or numeric values. For example, to return a list of customers whose last names begin with the letters A through M, use the SQL statement

 SELECT ID, FirstName, LastName  FROM tblCustomer WHERE LastName LIKE '[A-M]%' 

This query returns the five customers in the database whose last names begin with the letters A through M.

Note

If you're coming to SQL Server from Microsoft Access, you should know that the wildcard characters in Access SQL are different from the wildcards in standard SQL. In Access, you use an asterisk instead of a percent sign to match any number of characters, and you use a question mark instead of an underscore to match any single character. In standard SQL, you use a percent to match any number of characters, and you use an underscore to match any single character.

The IN Operator

The IN operator retrieves records that match a list of values. For example, to retrieve all the customers in either Colorado or Wisconsin, use

 SELECT FirstName, LastName, State  FROM tblCustomer WHERE State IN ('CO', 'WI') 

This query retrieves the three customers who live either in Wisconsin or Colorado. Thus you can get the same results with IN as you do with OR. Some developers prefer to use IN when applying multiple criteria because it makes for a somewhat tidier SQL statement.