LOGICAL Operators
At times, you need to search for records based on two or more conditions. In these situations, you can use logical operators to combine search conditions. The logical operators AND and OR are commonly used for this purpose. (The NOT operator mentioned in Table 8-2 is also a logical operator in Oracle 11g, but it’s used to reverse the meaning of search conditions rather than combine them.) Keep in mind that when a query executes, records can be filtered with WHERE clause conditions. In other words, each record in the table is compared with the stated condition. If the condition is TRUE when compared with a record, the record is included in the results.
SELECT title, pubid, category FROM books WHERE pubid = 3 AND category = 'COMPUTER'
SELECT title, pubid, category FROM books WHERE pubid = 3 OR category = 'COMPUTER'
Order of Logical Operators
- Arithmetic operations are solved first.
- Comparison operators (5, 4, ¼, LIKE, and so forth) are solved next.
- Logical operators have a lower precedence and are evaluated last—in the order NOT, AND, and OR.
Logical operators are always evaluated in the order NOT, AND, and OR. Parentheses can be used to override the evaluation order.
SELECT * FROM books WHERE (category = 'FAMILY LIFE' OR pubid=4) AND cost > 15;