Oracle IN Operator

This SQL tutorial focuses on the Oracle IN Operator, 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 IN Operator

In Oracle, The IN Operator is used to test whether a value is “in” a specified list.

WHERE  column_name IN (value, value, value ..)

 
 

Oracle IN Operator with Numeric Values

This Oracle IN example would return all products whose price equals 50, 80, or 90:

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price IN (50, 80, 90)

You can achieve the same result by using the OR operator:

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price = 50 
OR     unit_price = 80
OR     unit_price = 90

 

Oracle IN Operator with Character Values

This Oracle example would return all products whose name is equal to Tea, Coffee, or Sugar:

SELECT product_id, product_name, unit_price
FROM products
WHERE product_name IN ('Tea', 'Coffee', 'Sugar')

• The strings values must be enclosed within single quotes.
• In Oracle, string values are case-sensitive.
• You can achieve the same result by using the OR operator:

SELECT product_id, product_name, unit_price
FROM products
WHERE product_name = 'Tea'
OR    product_name = 'Coffee'
OR    product_name = 'Sugar'

 

Oracle IN Operator with Date Values

This Oracle example would return all customers whose birthdate is equal to 01.JAN.1990 or 07.SEP.1982:

SELECT customer_id, first_name, last_name, birthdate
FROM customers
 WHERE birthdate IN ('01-JAN-1990', '07-SEP-1982')

• Date values must be enclosed within single quotes (‘date value’).
• Date values are format sensitive.
• You can achieve the same result by using the OR operator:

SELECT customer_id, first_name, last_name, birthdate
FROM customers
WHERE birthdate = '01-JAN-1990'
OR    birthdate = '07-SEP-1982'

 

Oracle NOT Operator

• The Oracle NOT operator is used to produce opposite results than those produced by the operator IN.
• The NOT keyword should be written before the IN keywrod.

This Oracle example would return all customers who live in London, Paris or Berlin:

SELECT customer_id, first_name, last_name, birthdate, city
FROM customers
WHERE  city IN ('London', 'Paris', 'Berlin')

This Oracle example would return all customers who don’t live in London, Paris or Berlin

SELECT customer_id, first_name, last_name, birthdate, city
FROM customers
WHERE  city NOT IN ('London', 'Paris', 'Berlin')

The column data type must match the data type of the values within the round brackets.