Home arrow Oracle arrow Page 2 - Clauses and Logical Operators for Retrieving Table Data

How to use the AND, OR, and NOT logical operators - Oracle

In this sixth part of a nine-part series that focuses on retrieving data from tables with the SELECT statement, you'll learn how to code the WHERE clause and how to use comparison and logical operators. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

TABLE OF CONTENTS:
  1. Clauses and Logical Operators for Retrieving Table Data
  2. How to use the AND, OR, and NOT logical operators
By: Murach Publishing
Rating: starstarstarstarstar / 0
August 17, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 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 located in Springfield, New Jersey, are retrieved from the Vendors table. When you use the OR operator, though, only one of the conditions must be true. So in the second example, all vendors located in New Jersey and all the vendors located in Pittsburgh are retrieved.

The third example shows a compound condition that uses two NOT operators. As you can see, this expression is difficult to understand. Because of that, 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. As a result, 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 the two conditions connected by the OR operator are evaluated first.

The syntax of the WHERE clause with logical operators

WHERE [NOTsearch_condition_1 {AND|OR} [NOTsearch_condition_2 ...

Examples of queries using logical operators

A search condition that uses the AND operator

WHERE vendor_state 'NJ' AND vendor_city 'Springfield'

A search condition that uses the OR operator

WHERE vendor_state 'NJ' OR vendor_city 'Pittsburgh'

A search condition that uses the NOT operator

WHERE NOT (invoice_total >= 5000 OR NOT invoice_date < = '01-JUL-2008')

The same condition rephrased to eliminate the NOT operator

WHERE invoice_total 5000 AND invoice_date <= '01-JUL-2008'

A compound condition without parentheses

SELECT invoice_numberinvoice_dateinvoice_total
FROM invoices
WHERE invoice_date 
'01-MAY-2008' OR invoice_total 500
AND invoice_total payment_total credit_total 0
ORDER BY invoice_number 


(91 rows selected)

The same compound condition with parentheses

WHERE (invoice_date '01-MAY-2008' OR invoice_total 500)
AND 
invoice_total payment_total credit_total 0
ORDER BY invoice_number 


(39 rows selected)

Description

  1. 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.
  2. You can use the NOT operator to negate a condition, but that can make the search condition difficult to understand. If it does, you should rephrase the condition to eliminate NOT.
  3. When Oracle 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.
Please check back for the next part of the series.

 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: