MySQL IN Operator

This SQL tutorial focuses on the MySQL 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 MySQL. To read additional posts regarding this subject, please use the following links:

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

---

MySQL IN Operator

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

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

 

MySQL IN Operator with Numeric Values

This MySQL IN example would return all books whose price equals 50, 80, or 90:

SELECT bookID, bookName, bookPrice
FROM books
WHERE  bookPrice IN (50, 80, 90)

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

SELECT bookID, bookName, bookPrice
FROM books
WHERE  bookPrice = 50 
OR     bookPrice = 80
OR     bookPrice = 90

MySQL IN Operator with Character Values

This MySQL example would return all books who were written by Frank Herbert or Virginia Woolf.

SELECT bookID, bookName, bookPrice, author
FROM books
WHERE author IN (' Frank Herbert', 'Virginia Woolf')

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

SELECT bookID, bookName, bookPrice, author
FROM books
WHERE author   = 'Frank Herbert'
OR    author   = 'Virginia Woolf'

MySQL IN Operator with Date Values

This MySQL example would return all books who were published on January 1st, 1990 or September 7th, 1982.

SELECT bookName, author, PublicationDate
FROM books
 WHERE PublicationDate IN ('1990-01-01', '1982-09-07')

• 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 bookName, author, PublicationDate
FROM books
WHERE PublicationDate =  '1990-01-01' 
OR    PublicationDate =  '1982-09-07'

MySQL NOT Operator

• The MySQL 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 MySQL example would return all students who live in London, Paris or Berlin:

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

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

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

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