Boolean Logic in SQL
This chapter is from the book
This chapter is from the book
This chapter is from the book
Keywords Introduced
AND • OR • NOT • BETWEEN • IN • IS NULL
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 several 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 for which the QuantityPurchased column has a value of 5. In the real world, the selection of data is often much less 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 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 principal 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.