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.