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:


 

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.

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…