Network Computingwww.networkcomputing.com

Filtering Data

We now know how to query a table for records using the SELECT statement. You may have noticed that, to this point, whenever we have run a query, we have returned all of the records in the table. If the table is small, say thirty records, then this may not be a big problem. But what if the table contains a million records? Running a query against such a table will take time and monopolize resources. We will probably still need some way to filter for the specific information that we want.

SQL provides a way for us to filter our data to produce smaller result sets. The WHERE clause is used in a SQL statement to specify that we want all of the records that fit specific criteria.

Syntactically, the WHERE clause comes after the FROM clause:

SELECT
 Field1, Field2, Field3, Fieldn
FROM
 Table
WHERE
 Condition

A very simple example of using a WHERE clause here where we want a list of all the employees that live in Washington.

The Customer table has a field called Region. This field is used for state information that only US and Canada customers use.

SELECT
 EmployeeID,
 LastName,
 FirstName
FROM
 Employees
WHERE
 Region = 'WA'

(5 row(s) affected)

There are several comparison operators that you can use in search criteria. Below is a list of valid operators that we can use in the WHERE clause of a SQL statement:

*NULL is used to describe a value which is unknown, as in the gender of an unborn baby. We'll discuss this operator further a little later in the chapter.

Let's look at a practical example of using the WHERE clause. Suppose we have been asked to find the first and last name of everyone in the Employee table who was hired after 1/1/1994.

We already know how to construct the SQL statement to get the names of everyone:

SELECT FirstName, LastName, HireDate
FROM Employees

Now, let's add the WHERE clause to meet our hire date criteria. In order for this query to work properly we need to be sure that we use the proper operator. Since we are looking for everyone hired after 1/1/1994 we will use the greater than operator (>):

SELECT FirstName, LastName, HireDate
FROM Employees
WHERE HireDate > '1/1/1994'

Note that we need to enclose the date in single quotes. If we were working in another database such as Microsoft Access we might need to use other delimiters. Access, for instance, uses the pound symbol (#) for enclosing dates. Check with your individual provider to determine which delimiters you should use. If you're working in Oracle, this date format will not work. The single quotes are fine, but you will have to write '1-JAN-1994' as in the following example, as this is how Oracle recognizes dates:

SELECT FirstName, LastName, HireDate
FROM Employees
WHERE HireDate > '1-JAN-1994';

When we run this query, we will have fewer records returned than are actually in the table:

Remember that we need to enclose dates in single quotation marks in SQL Server and in the pounds (#) in Microsoft Access.