7. Boolean Logic
We introduced the concept of selection criteria in the previous chapter, but only in its simplest form. We’ll now expand on that concept to greatly enhance our ability to specify the rows returned from a SELECT. This is where the pure logic of SQL comes into play. In this chapter, we’ll introduce a number of operators that will allow you to create complex logical expressions.
Given these new capabilities, if someone should request a list of all female customers who live in zip codes 60601 through 62999 but excluding anyone under the age of 30 or who doesn’t have an email address, that will be something you can provide.
Complex Logical Conditions
The WHERE clause introduced in the previous chapter used only simple selection criteria. We saw clauses such as:
WHERE QuantityPurchased = 5
The condition expressed in this WHERE clause is quite basic. It specifies merely to return all rows where the QuantityPurchased column has a value of 5. In the real world, the selection of data is often far from this straightforward. Accordingly, let’s now turn our attention to methods of specifying some more complex logical conditions in selection criteria.
The ability to devise complex logical conditions is sometimes called Boolean logic. This term, taken from mathematics, refers to the ability to formulate complex conditions that are evaluated as either true or false. In the aforementioned example, the condition QuantityPurchased = 5 is evaluated as either true or false for each row in the table. Obviously, we want to see only rows where the condition evaluates as true.
The principle keywords used to create complex Boolean logic are AND, OR, and NOT. These three operators are used to provide additional functionality to the WHERE clause. In proper combination, the AND, OR, and NOT operators, along with parentheses, can specify just about any logical expression that can be imagined.
The AND Operator
The following examples will be taken from this Purchases table:
Here’s an example of a WHERE clause that uses the AND operator:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased > 3 AND QuantityPurchased < 7
The AND clause means that all conditions must evaluate to true for the row to be selected. This SELECT specifies that the only rows to be retrieved are those for which the QuantityPurchased is both greater than 3 and less than 7. Therefore, only these two rows are returned:
Notice that the row for Sandy Harris is not returned. Why? Sandy purchased a quantity of 10, which, in fact, does satisfy the first condition (QuantityPurchased > 3). However, the second condition (QuantityPurchased < 7) is not satisfied and therefore is not true. When using the AND operator, all conditions specified must be true for the row to be selected.
The OR Operator
Let’s now look at the OR operator. The AND operator meant that all conditions must evaluate to true for the row to be selected. The OR operator means that the row will be selected if any of the conditions are determined to be true.
Here’s an example, taken from the same table:
SELECT CustomerName, QuantityPurchased, PricePerItem FROM Purchases WHERE QuantityPurchased > 8 OR PricePerItem > 3
This SELECT returns this data:
Why are the rows for Sandy Harris and James Turban displayed, and not the row for Kim Chiang? The row for Sandy Harris is selected because it meets the requirements of the first condition (QuantityPurchased > 8). It doesn’t matter that the second condition (PricePerItem > 3) isn’t true, because only one condition needs to be true for an OR condition.
Likewise, the row for James Turban is selected because the second condition (PricePerItem > 3) is true for that row. The row for Kim Chiang isn’t selected because it doesn’t satisfy either of the two conditions.
Using Parentheses
Let’s say that we are interested only in orders from customers from either the state of Illinois or the state of California. Additionally, we want to see orders only where the quantity purchased is greater than 8. To attempt to satisfy this request, we might put together this SELECT statement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State = ‘IL’ OR State = ‘CA’ AND QuantityPurchased > 8
We would expect this statement to return only one row of data, for Sandy Harris. Although we have two rows for customers in Illinois or California (Chiang and Harris), only one of those (Harris) has a quantity purchased greater than 8. However, when this statement is executed, we get the following:
We see two rows instead of the expected one. What went wrong? The answer lies in how SQL interprets the WHERE clause, which happens to contain both AND and OR operators. Like other computer languages, SQL has a predetermined order of evaluation that specifies the order in which various operators are interpreted. Unless told otherwise, SQL always processes the AND operator before the OR operator. So, in the previous statement, it first looks at the AND, and evaluates the condition:
State = ‘CA’ AND QuantityPurchased > 8
The row that satisfies that condition is for Sandy Harris. SQL then evaluates the OR operator, which allows for rows where the State equals IL. That adds the row for Kim Chiang. The result is that SQL determines that both the Kim Chiang and the Sandy Harris rows meet the condition.
Obviously, this isn’t what was intended. This type of problem often comes up when AND and OR operators are combined in a single WHERE clause. The way to resolve the ambiguity is to use parentheses to specify the desired order of evaluation. Anything in parentheses is always evaluated first.
Here’s how parentheses can be added to the previous SELECT to correct the situation:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE (State = ‘IL’ OR State = ‘CA’) AND QuantityPurchased > 8
When this is executed, we see this data:
The parentheses in the SELECT statement force the OR expression (State = ‘IL’ OR State = ‘CA’) to be evaluated first. This produces the intended result.
Multiple Sets of Parentheses
Let’s say we want to select two different sets of rows from the Purchases table: first, rows for customers in New York, and second, rows for customers in Illinois who have made a purchase with a quantity between 3 and 10. The following SELECT accomplishes this requirement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State = ‘NY’ OR (State = ‘IL’ AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10))
The result is:
Notice that there are two sets of parentheses in this statement, with one set inside the other. This use of parentheses is analogous to the parentheses used in the composite functions seen in Chapter 4. When there is more than one set of parentheses in functions, the innermost set is always evaluated first. The same is true of parentheses used in Boolean expressions. In this example, the innermost set of parentheses contains:
(QuantityPurchased >= 3 AND QuantityPurchased <= 10)
After this condition is evaluated for each row, the logic proceeds outward to the second set of parentheses:
(State = ‘IL’ AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10))
Finally, the logic adds in the final line of the WHERE clause (not enclosed in parentheses) regarding the state of New York:
WHERE State = ‘NY’ OR (State = ‘IL’ AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10))
In essence, SQL’s logic first evaluated expressions in the innermost set of parentheses, then the outer set of parentheses, and then all remaining expressions.
The NOT Operator
In addition to the AND and OR operators, the NOT operator is often useful for expressing a complex logical condition. The NOT expresses a negation, or opposite, of whatever follows the NOT. Here’s a simple example:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE NOT State = ‘NY’
The result is:
This specifies a selection of rows for which the state is not equal to NY. In this simple case, the NOT operator is not truly necessary. The logic of the previous statement can also be accomplished via the following equivalent statement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State <> ‘NY’
Here, the not equals operator (<>) accomplishes the same thing as the NOT operator. Here’s a more complex example with the NOT operator:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE NOT (State = ‘IL’ OR State = ‘NY’)
The result is:
When the NOT operator is used before a set of parentheses, it negates everything in the parentheses. In this example, we’re looking for all rows for which the state is not Illinois or New York.
Again, note that the NOT operator is not strictly necessary in this example. The logic of the previous query can also be accomplished with the following equivalent statement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State <> ‘IL’ AND State <> ‘NY’
It might take a bit of reflection to understand why the preceding two statements are equivalent. The first statement uses the NOT operator and a logical expression with an OR operator. The second statement converts the logic into an expression with an AND operator.
Here’s a final example of how the NOT operator can be used in a complex statement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE NOT (State = ‘IL’ AND QuantityPurchased > 3)
This query is saying to select customers where it’s not true that the state equals Illinois and has a QuantityPurchased greater than 3. The NOT operator applies to the entire logical expression that the state equals Illinois and has a QuantityPurchased greater than 3. The result is:
These two rows were selected because the only customer in Illinois who also has a QuantityPurchased greater than 3 is Kim Chiang. Because we’re applying a NOT to this entire logic, the end result is the display of the other two customers.
Once again, this query can be expressed in an alternate way without using the NOT:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State <> ‘IL’ OR QuantityPurchased <= 3
As seen in these examples, it may not be logically necessary to use the NOT operator in complex expressions with arithmetic operators such as equals (=) or less than (<). However, it’s often more straightforward to place a NOT in front of a logical expression rather than attempting to convert that expression into one that doesn’t use the NOT. In other words, the NOT operator can provide a convenient and useful way of expressing one’s logical thoughts.
The BETWEEN Operator
Let’s now turn to two special operators, BETWEEN and IN, that can simplify expressions that would ordinarily require the OR or AND operators. The BETWEEN operator allows us to abbreviate an AND expression with greater than or equal to (>=) and less than or equal to (<=) operators in an expression with a single operator.
Let’s say, for example, that we want to select all rows with a quantity purchased from 5 to 20. One way of accomplishing this is with the following SELECT statement:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased >= 5 AND QuantityPurchased <= 20
Using the BETWEEN operator, the equivalent statement is:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased BETWEEN 5 AND 20
In both cases, the SELECT returns this data:
The BETWEEN operator always requires a corresponding AND placed between the two numbers.
Note the relative simplicity of the BETWEEN operator. Also notice that the BETWEEN is inclusive of the numbers specified. In this example, BETWEEN 5 AND 20 includes the numbers 5 and 20. Thus, the BETWEEN is equivalent only to the greater than or equal to (>=) and less than or equal to (<=) operators. It can’t be used to express something simply greater than (>) or less than (<) a range of numbers. The row for James Turban is selected because the quantity purchased is equal to 5, and therefore is between 5 and 20.
The NOT operator can be used in conjunction with BETWEEN. For example, this SELECT:
SELECT CustomerName, QuantityPurchased FROM Purchases WHERE QuantityPurchased NOT BETWEEN 5 AND 20
retrieves this data:
The IN Operator
Just as BETWEEN represents a special case of the AND operator, the IN operator allows for a special case of the OR operator. Let’s say we want to see rows where the state is Illinois or New York. Without the IN operator, this can be accomplished with this statement:
SELECT CustomerName, State FROM Purchases WHERE State = ‘IL’ OR State = ‘NY’
Here is an equivalent statement using the IN operator:
SELECT CustomerName, State FROM Purchases WHERE State IN (‘IL’, ‘NY’)
In either case, the data retrieved is:
The IN operator requires a subsequent listing of values enclosed within parentheses. Commas must be used to separate all values.
The usefulness of the IN operator may not be obvious in this example, where only two states are listed. However, the IN can just as easily be used in situations involving dozens of specific values. This greatly reduces the amount of typing required for such a statement. Another handy use for the IN operator comes in situations where values are obtained from an Excel spreadsheet. To obtain multiple values from adjacent cells in a spreadsheet for a SQL statement, knowledgeable Excel users can copy those values into a CSV file with a comma delimiter. The values can then be pasted inside parentheses following the IN operator.
As with the BETWEEN operator, the NOT operator can be used with the IN operator, as shown in this example:
SELECT CustomerName, State FROM Purchases WHERE State NOT IN (‘IL’, ‘NY’)
This retrieves this data:
One final note about the IN operator. There is a second way to use the IN operator that is substantially different from the syntax just discussed. In the second format, an entire SELECT statement is specified within parentheses, allowing the individual values to be created logically when needed. This is called a subquery, which will be covered in detail in Chapter 14.
Boolean Logic and NULL Values
At the beginning of this chapter, we stated that the Boolean logic in SQL evaluates complex expressions as either true or false. This assertion was not completely accurate. When evaluating the conditions in a WHERE clause, there are actually three possibilities: true, false, and unknown. The possibility of an unknown value derives from the fact that columns in SQL databases are sometimes allowed to have a NULL value. As mentioned in Chapter 1, NULL values are those for which there is an absence of data.
SQL provides a special keyword to test for the presence of NULL values for a column specified in a WHERE clause. The keyword is IS NULL. Let’s look at an example taken from the Products table seen previously:
For this example, we’ll imagine that as rows are added to the Products table, they are initially not given a weight. The Weight column is initially given a value of NULL, and a user later assigns a weight to the product.
Let’s say that we attempt to use the following SELECT to find products missing a weight:
SELECT Description, Weight FROM Products WHERE Weight = 0
This would return:
Obviously, this is not quite what we wanted. A weight of zero is not the same as a weight with a NULL value. To correct this, we need to issue:
SELECT Description, Weight FROM Products WHERE Weight = 0 OR Weight IS NULL
This returns:
The IS NULL keyword can also be negated as IS NOT NULL, which allows for the retrieval of rows that do not have NULL for the specified column.
Note that the ISNULL function, discussed in Chapter 4, can provide an alternative to the IS NULL keyword. The equivalent of the previous SELECT statement, using the ISNULL function, is:
SELECT Description, Weight FROM Products WHERE ISNULL(Weight, 0) = 0
This SELECT retrieves the same two rows. The ISNULL function converts all values for the Weight column with a value of NULL to 0. This produces the same result as the previous statement, which tested for a value of 0 or NULL.
The ISNULL function and IS NULL keywords can also be used together, as in this statement:
SELECT Description, ISNULL(Weight, 0) AS ‘Weight’ FROM Products WHERE Weight = 0 OR Weight IS NULL
This produces this data:
Looking Ahead
This chapter covered the important topic of how to create complex expressions of selection logic. The basic Boolean operators used in this endeavor were AND, OR, and NOT. We also discussed the BETWEEN and IN operators, which allow for a more concise statement of the AND and OR operators in certain situations. Parentheses are another essential tool in the formulation of complex expressions. By using parentheses, you can create almost every imaginable logical condition. Finally, we discussed how to deal with NULL values when selecting data.
In our next chapter, we’ll revisit the columnlist and explore an important construct that will allow us to inject logic into the individual columns in the columnlist. This is referred to as conditional logic. By using the same Boolean logic operators discussed in this chapter, along with a few additional keywords, we’ll be able to specify logic that determines how individual columns in the columnlist are displayed.