This chapter is from the book
This chapter is from the book
This chapter is from the book
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 condition follows the NOT. The condition can be anything from the evaluation of the value of a single column to a complex expression within parentheses. Here’s an example of a NOT used with a simple condition:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE NOT State = 'NY'
The result is:
CustomerName |
State |
QuantityPurchased |
Kim Chiang |
IL |
4 |
Sandy Harris |
CA |
10 |
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:
CustomerName |
State |
QuantityPurchased |
Sandy Harris |
CA |
10 |
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 >= 4)
This query is saying to select customers where it’s not true that the state equals Illinois and has a QuantityPurchased greater than or equal to 4. The NOT operator applies to the entire logical expression that the state equals Illinois and has a QuantityPurchased greater than or equal to 4. The result is:
CustomerName |
State |
QuantityPurchased |
Sandy Harris |
CA |
10 |
James Turban |
NY |
5 |
These two rows were selected because the only customer in Illinois who also has a QuantityPurchased greater than or equal to 4 is Kim Chiang. Because we’re applying a NOT to this entire logic, the 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 < 4
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.