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:
Car Model | Price | Supplier ID |
Ford | 10000 | 90 |
Mazda | 9000 | 80 |
Chevrolet | 8000 | 70 |
Pontiac | 7000 | 70 |
Nissan | 5000 | 70 |
Skoda | 4000 | 40 |
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