Oracle AND & OR Operators

This SQL tutorial focuses on the Oracle AND & OR operators, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in Oracle. To read additional posts regarding this subject, please use the following links:

• Introduction – Oracle WHERE clause
• Simple comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Oracle AND & OR Operators

The purpose of the Oracle AND & OR operators is to allow filtering based on multiple conditions. Using these operators, you can combine the result of two (or more) conditions to produce a single result based on them, for example:

Oracle AND Operator

AND requires all conditions to be true.

SELECT *
FROM car_models
WHERE price > 6000
AND
      supplier_id = 70

AND indicates that all conditions must be met – the car models whose price is higher than 6000 and whose supplier number equals 70.

The car models who meet both these conditions are: Chevrolet and Pontiac (2 car models in total).
 

Oracle OR Operator

OR requires either condition to be true.

SELECT *
FROM car_models
WHERE price > 6000
OR
      supplier_id = 70

OR indicates that at least one of the conditions must be met – the car models whose price is higher than 6000 or whose supplier number equals 70.

• For Skoda – none of these conditions is met.
• For Nissan, the supplier number is 70 – one condition is met.
• For Chevrolet and Pontiac, price is higher than 6000 and the supplier number is 70 – both conditions are met.
• For Ford and Mazda, price is higher than 6000 – one condition is met.

5 car models meet at least one of these conditions.
 

Note:

The WHERE keyword appears only once. After the AND operator or the OR operator, specify the additional condition without specifying the WHERE keyword again.

SELECT *
FROM car_models
WHERE price > 6000
OR
WHERE supplier_id = 70
-- (Error)

SELECT * 
FROM car_models 
WHERE price > 6000
OR 
      supplier_id = 70
-- Valid statement

Order of Precedence

The Oracle AND operator takes precedence over the Oracle OR operator (just like a multiplication operation takes precedence over an addition operation).

The following Oracle SELECT statement is used for displaying the customers who:

(First condition) Live in London.

Or:

(Second condition) Live in Paris and whose age is higher than 30.

SELECT customer_id, first_name, city, age
FROM customers
WHERE    city = 'London'
OR
         City = 'Paris' AND age > 30

Changing the Order of Precedence

Changing the order of priorities between the Oracle OR and AND operators requires round brackets  (just like round brackets change the order of priorities between the mathematical addition and multiplication operations).

The following Oracle SELECT statement is used for displaying the customers who:

(First condition) Live in London OR Paris

And

(Second condition) their age is higher than 30

SELECT customer_id, first_name, city, age
FROM customers
WHERE    (city = 'London' OR City = 'Paris')
AND     age > 30