Learning SQL, 3rd Edition

Chapter 4. Filtering

Sometimes 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 the SQL data statements (except the insert statement) include an optional where clause containing one or more 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 explores the various types of filter conditions that you can employ in the where clauses of select, update, and delete statements; I demonstrate the use of filter conditions in the having clause of a select statement in Chapter 8.

Condition Evaluation

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

WHERE first_name = 'STEVEN' AND create_date > '2006-01-01'

Given these two conditions, only rows ...