Using the WHERE Clause to Select Particular Rows

So far, we have looked at selecting all data from a table and selecting particular columns. Next, we will consider how to select particular rows. This is useful because we frequently want to select records from a table or tables that match particular search criteria. This becomes more important when we need to retrieve a few useful rows from a much larger table.

We can accomplish this using the WHERE clause of the SELECT statement. A simple example follows:

select employeeID, name
from employee
where job='Programmer';

(Remember, by the way, that we can set queries out on multiple lines. Each query is terminated by a semicolon. We lay out the SELECT statement like this to make it easier to read.)

The results of running this query on the employee database are as shown here:

+-------------+--------------+
| employeeID | name |
+-------------+--------------+
| 6651 | Ajay Patel |
| 7513 | Nora Edwards |
+-------------+--------------+
2 rows in set (0.42 sec)

We used a condition in the WHERE clause to match only the rows in the table that met the specified criteria—in this case, they had to be employed as programmers.

Notice that we have combined this with a specific list of desired columns (employeeID and name) to pull out only the information we are interested in.

In this case, we have used a test of equality in the WHERE clause. Note that SQL uses = for testing equality. This is different from various other languages that use == or eq.

A huge variety of functions are available for use in the WHERE clause, and we will address these in detail in Chapter 8. For the time being, we will mention only the most commonly used operators:

Equality, or =, which we have seen used previously.

Inequality, expressed as != or <>.

All the permutations of > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to).

IS NULL and IS NOT NULL, which are used to test whether a value is or is not NULL. You cannot do this by testing whether somevalue=NULL. (We will discuss why in Chapter 8.)

The arithmetic operators you would expect, typically used in conjunction with comparison operators. For example, we might like to test whether somevalue > someothervalue*10.

The standard Boolean operators AND, OR, and NOT, which we can use to group tests together. These are lower in precedence than the comparison operators, so, for example, salary > 30000 AND salary < 50000 works as you would expect.

In addition to the operators, we will use one function in some examples. The count() function allows us to count the number of rows returned by a query. For example:

select count(*) from employee;

This query will tell us how many rows there are in the employee table.

Finally, we can control precedence by grouping expressions with parentheses.

An example of a slightly more complex query using WHERE is as follows:

select * from assignment
where employeeID=6651 and hours > 8;

This query selects all the work assignments performed by employeeID 6651 (Ajay Patel) in which he performed more than 8 hours of work.

One important point to note is that we are not allowed to use any column aliases in the WHERE clause. We must use the original column name. This is an ANSI SQL limitation. The reason for it is that the value of the aliased column may be unknown at the time the WHERE condition is examined.