Learning Snowflake SQL and Scripting

Chapter 2. Filtering

There are cases when you will want to deal with every row in a table, such as:

• Removing all rows from a table used for nightly data loads
• Modifying all rows in a table after adding a new column
• Retrieving all rows from a message-queue table

Most of the time, however, you will want to focus on a subset of the rows in a table by including a where clause. Along with the select statement used for querying data, several other SQL data statements, including update and delete, include a where clause consisting of one or more filter conditions used to narrow the statement’s focus. As shown in Chapter 1, select statements can also perform filtering in the having and qualify clauses, making filtering one of the most common activities when writing SQL data statements. This chapter explores the many different types of conditions available for filtering data.

Condition Evaluation

The where clause consists of one or more conditions separated by the and and or operators. If there is a single condition, it must evaluate as true in order for a row to be included in the result set. If there are two or more conditions, there are multiple possible outcomes:

• If the conditions are separated by the and operator, then all of the conditions must evaluate as true.
• When the or operator is used, only one condition must evaluate as true.

Let’s say you are querying a directory to look up an old friend and use the following where clause:

where last_name = 'SMITH' and state = 'CA' ...