How to Retrieve Data from a Single Table - How to Use the AND, OR, and NOT Logical Operators
(Page 11 of 17 )
Figure 11 shows how to use logical operators in a WHERE clause. You can use the AND and OR operators to combine two or more search conditions into a compound condition. And you can use the NOT operator to negate a search condition. The examples in this figure illustrate how these operators work.
The first two examples illustrate the difference between the AND and OR operators. When you use the AND operator, both conditions must be true. So, in the first example, only those vendors in New Jersey whose year-to-date purchases are greater than 200 are retrieved from the Vendors table (2 rows). When you use the OR operator, though, only one of the conditions must be true. So, in the second example, all the vendors from New Jersey and all the vendors whose year-to-
date purchases are greater than 200 are retrieved (76 rows).
The third example shows a compound condition that uses two NOT operators. As you can see, this expression is somewhat difficult to understand. Because of that, and because using the NOT operator can reduce system performance, you should avoid using this operator. The fourth example in this figure, for instance, shows how the search condition in the third example can be rephrased to eliminate the NOT operator. Notice that the condition in the fourth example is much easier to understand.
The last two examples in this figure show how the order of precedence for the logical operators and the use of parentheses affect the result of a search condition. By default, the NOT operator is evaluated first, followed by AND and then OR. However, you can use parentheses to override the order of precedence or to clarify a logical expression, just as you can with arithmetic expressions. In the next to last example, for instance, no parentheses are used, so the two conditions connected by the AND operator are evaluated first. In the last example, though, parentheses are used so that the two conditions connected by the OR operator are evaluated first. If you take a minute to review the results shown in this figure, you should quickly see how these two conditions differ.
Figure 11: The syntax of the WHERE clause with logical operators
WHERE [NOT] search_condition_1 {AND|OR} [NOT] search_condition_2 ...
Examples of queries using logical operators:
A search condition that uses the AND operator
WHERE VendorState = 'NJ' AND YTDPurchases > 200
A search condition that uses the OR operator
WHERE VendorState = 'NJ' OR YTDPurchases > 200
A search condition that uses the NOT operator
WHERE NOT (InvoiceTotal >= 5000 OR NOT InvoiceDate <= '2002-07-01')
The same condition rephrased to eliminate the NOT operator
WHERE InvoiceTotal < 5000 AND InvoiceDate <= '2002-07-01'
A compound condition without parentheses:
WHERE InvoiceDate > '05/01/2002'
OR InvoiceTotal > 500
AND InvoiceTotal - PaymentTotal - CreditTotal > 0
(91 rows)
The same compound condition with parentheses:
WHERE (InvoiceDate > '05/01/2002'
OR InvoiceTotal > 500)
AND InvoiceTotal - PaymentTotal - CreditTotal > 0
(39 rows)
Description
- You can use the AND and OR logical operators to create compound conditions that consist of two or more conditions. You use the AND operator to specify that the search must satisfy both of the conditions, and you use the OR operator to specify that the search must satisfy at least one of the conditions.
- You can use the NOT operator to negate a condition. Because this operator can make the search condition unclear, you should rephrase the condition if possible so it doesn't use NOT.
- When SQL Server evaluates a compound condition, it evaluates the operators in this sequence: (1) NOT, (2) AND, and (3) OR. You can use parentheses to override this order of precedence or to clarify the sequence in which the operations will be evaluated.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Use the IN Operator >>
More MS SQL Server Articles
More By Bryan Syverson
