Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.5.4. Filtering Information

Very often, you need to narrow your search of rows within a table to specific information. In other words, instead of retrieving all the rows, you want to retrieve specific rows based on specific search criteria.

For example, you might be asked for the e-mail address of a person named Dobney. You know the information is in the Person.Person table and Person.EmailAddress tables. You could run the following query and tediously look through the 19,000 entries until you found Dobney.

SELECT p.LastName, p.FirstName, e.EmailAddress
FROM Person.Person as p INNER JOIN
     Person.EmailAddress as e
     ON p.BusinessEntityID = e.BusinessEntityID

Or, you could add a WHERE clause that filters the query based on the LastName column. The following query returns a single row.

SELECT p.LastName, p.FirstName, e.EmailAddress
FROM Person.Person as p INNER JOIN
     Person.EmailAddress as e
     ON p.BusinessEntityID = e.BusinessEntityID
   WHERE p.LastName = 'Dobney'

In this query, the database engine examines the LastName column in every row in the Person table. If the last name is Dobney, then the row is added to the result set. If multiple rows include the last name of Dobney, then each row is included in the result set.

The syntax of the WHERE clause is

WHERE column name <search condition>

Multiple search conditions are possible. These are in different categories:

• Comparison operators: Using equal and not equal comparisons, such as =, <, and so on.

• String operators: Using LIKE and