4.1. Conditions

The WHERE clause is all about true conditions. Its basic syntax is:

WHERE condition that evaluates as TRUE

As we’ve learned, a condition is some expression that can be evaluated by the database system. The result of the evaluation will be one of TRUE, FALSE, or UNKNOWN. We’ll cover these one at a time, starting with TRUE.

4.1.1. Conditions that are True

A typical WHERE condition looks like this:

SELECT
  name
FROM
  teams
WHERE
  id = 9

In this query, as we now know from Chapter 3, the result set produced by the FROM clause consists of all the rows of the teams table. After the FROM clause has produced a result set, the WHERE clause filters the result set rows, using the id = 9 condition. The WHERE clause evaluates the truth of the condition for every row, in effect comparing each row’s id column value to the constant value 9. The really neat part about this evaluation is that it happens all at once. You may think of the database system actually examining one value after another, and this mental picture is really not too far off the mark. There is, however, no sequence involved; it is just as correct to think of it happening on all rows simultaneously.

So what is the end result? No doubt you’re ahead of me here. Amongst all the rows in the teams table, the given condition will be TRUE for only one of them. For all the other rows, it will be FALSE. All the other rows are said to be filtered out by the WHERE condition.

4.1.2. When “Not True” is Preferable

But what if we want the other rows? Suppose we want the names of all teams who aren’t team 9?

There are two approaches:

• WHERE NOT id = 9

  The NOT keyword inverts the truthfulness of the condition.

• WHERE id <> 9

  This is the not equals comparison operator. You can, if you wish, read it as “less than or greater than,” and this would be accurate.

Notice what we’ve done in both cases. We want all rows where the condition id = 9 is FALSE, but we wrote the WHERE clause in such a way that the condition evaluates as TRUE for the rows we want, in keeping with the general syntax:

WHERE condition that evaluates as TRUE
				

More specifically, the WHERE clause condition can include a NOT keyword, and, as we shall see in a moment, several conditions that are logically connected together to form a compound condition.

Besides TRUE and FALSE, there is one other result that’s possible when a condition is evaluated in SQL: UNKNOWN.

A condition evaluates as UNKNOWN if the database system is unable to figure out whether it’s TRUE or FALSE. In order to see UNKNOWN in action, we’ll need a good example, and for that, we’ll use yet another of our sample applications.

---

Note:

You would expect that with a concept as simple as equals or not equals, everything should work the same way. Regrettably, MySQL handles this slightly differently to the perceived norm. Let’s recap the scenario: we want all rows where id is not equal to 9.

The first way is to say NOT id = 9, which we expect to be TRUE for every row except one. Unfortunately, MySQL applies the NOT to the id column value first, before comparing to 9. In effect, MySQL evaluates it as:

WHERE ( NOT id ) = 9

MySQL—for reasons we’ll not go into—treats 0 and FALSE interchangeably, and any other number as TRUE, which it equates with 1. If id actually had the value of 0 (which no identifier should), then NOT id would be 1. For all other values, NOT id would be 0. And 0 isn’t equal to 9.

Be careful using NOT. Unless you’re sure, enclose whatever comes after NOT in parentheses. The following will work as expected in all database systems:

WHERE NOT ( id = 9 ) 

A better choice is to avoid using NOT altogether. Just use the not equals operator:

WHERE id <> 9 

Also, avoid using MySQL’s version of the not equals operator, shown below:

WHERE id != 42 

Note that using != is specific to MySQL and incompatible with other database systems.

---