Learning SQL

Chapter 4. Filtering

There are some instances when you will want to work with every row in a table, such as:

• Purging all data from a table used to stage new data warehouse feeds.

• Modifying all rows in a table after a new column has been added.

• Retrieving all rows from a message queue table.

In cases like these, your SQL statements won't need to have a where clause, since you don't need to exclude any rows from consideration. Most of the time, however, you will want to narrow your focus to a subset of a table's rows. Therefore, all of the SQL data statements (except the insert statement) include an optional where clause to house all filter conditions used to restrict the number of rows acted on by the SQL statement. Additionally, the select statement includes a having clause in which filter conditions pertaining to grouped data may be included. This chapter will explore the various types of filter conditions that can be employed in the where clauses of select, update, and delete statements.

Condition Evaluation

A where clause may contain one or more conditions, separated by the operators and and or. If there are multiple conditions separated only by the and operator, then all of the conditions must evaluate to true for the row to be included in the result set. Consider the following where clause:

    WHERE title = 'Teller' AND start_date < '2003-01-01'

Given these two conditions, any employee who is either not a teller or began working for the bank in 2003 or later will be removed from consideration. ...