The MySQL IN and NOT IN is a logical operator that allows you to specify a list of values in a WHERE clause. IN operator is used to determine whether a given value matches any value in a list or set of values. Essentially, it checks whether a value is within a specified set of values and returns TRUE if a match is found and FALSE otherwise. And NOT IN is used to check if a specified value does not match any value in a list of values.
MySQL IN Operator
The IN operator is useful for comparing a value against a list of values. It's especially handy when you have several values to compare and want to avoid multiple OR conditions.
Syntax of IN Operator
value IN (value1, value2, value3, ...)- value: This is the value to test against the list. It can be a literal, a column, or an expression.
- value1, value2, ...: These are the values in the list you're comparing against.
The IN operator returns TRUE (1) if the value matches any value in the list. Otherwise, it returns FALSE (0).
Equivalence to OR Conditions
Using IN is like using multiple OR conditions:
value = value1 OR value = value2 OR value = value3 OR ...Examples of MySQL IN Operator
Let's consider a books table with columns title, author, and genre for our examples.
Example 1: Basic IN Operation
SELECT 'Mystery' IN ('Romance', 'Science Fiction', 'Mystery');Output Example:
+-------------------------------------------+
| 'Mystery' IN ('Romance', 'Science Fiction', 'Mystery') |
+-------------------------------------------+
| 1 |
+-------------------------------------------+Example 2: IN Operation with No Match
SELECT 'Biography' IN ('Romance', 'Science Fiction', 'Mystery');Output Example:
+-------------------------------------------+
| 'Biography' IN ('Romance', 'Science Fiction', 'Mystery') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+Handling NULL with IN
- NULL on Left Side: Returns NULL if the value to check is NULL.
- NULL in List: Returns NULL if the value doesn't match any list value and there's a NULL in the list.
Example 3: IN with NULL Values
SELECT NULL IN ('Romance', 'Science Fiction', NULL);Output Example:
+---------------------------------------+
| NULL IN ('Romance', 'Science Fiction', NULL) |
+---------------------------------------+
| NULL |
+---------------------------------------+Practical Use of IN in Queries
Example 4: Filtering Data with IN
To find books of certain genres:
SELECT title, genre
FROM books
WHERE genre IN ('Mystery', 'Science Fiction');Output Example:
+------------------+-----------------+
| title | genre |
+------------------+-----------------+
| Moonlit Shadows | Mystery |
| Starbound Journey | Science Fiction |
...MySQL NOT IN Operator
The NOT IN operator is essentially the negation of the IN operator. It returns TRUE if the specified value does not match any value in the given list.
Syntax of NOT IN Operator
value NOT IN (value1, value2, value3, ...)- value: This is the value you're testing.
- value1, value2, ...: These are the values in the list you're comparing against.
The NOT IN operator returns TRUE (1) if the value does not match any value in the list, and FALSE (0) otherwise.
Examples of MySQL NOT IN Operator
Let's consider a movies table with columns title, director, and genre for our examples.
Example 1: Basic NOT IN Operation
SELECT 'Comedy' NOT IN ('Drama', 'Thriller', 'Action');Output Example:
+-------------------------------------------+
| 'Comedy' NOT IN ('Drama', 'Thriller', 'Action') |
+-------------------------------------------+
| 1 |
+-------------------------------------------+Example 2: NOT IN Operation with Match
SELECT 'Drama' NOT IN ('Drama', 'Thriller', 'Action');Output Example:
+------------------------------------------+
| 'Drama' NOT IN ('Drama', 'Thriller', 'Action') |
+------------------------------------------+
| 0 |
+------------------------------------------+Handling NULL with NOT IN
- NULL on Left Side: Returns NULL if the value to check is NULL.
Example 3: NOT IN with NULL Values
SELECT NULL NOT IN ('Drama', 'Thriller', NULL);Output Example:
+---------------------------------------+
| NULL NOT IN ('Drama', 'Thriller', NULL) |
+---------------------------------------+
| NULL |
+---------------------------------------+Practical Use of NOT IN in Queries
Example 4: Filtering Data with NOT IN
To find movies not directed by either 'Steven Spielberg' or 'Christopher Nolan':
SELECT title, director
FROM movies
WHERE director NOT IN ('Steven Spielberg', 'Christopher Nolan');Output Example:
+-----------------+-----------------+
| title | director |
+-----------------+-----------------+
| Moonlight | Barry Jenkins |
| La La Land | Damien Chazelle |
...Summary
- Use IN to check if a value matches any in a set of values.
- It simplifies queries by replacing multiple OR conditions.
- Especially useful in WHERE clauses for filtering data based on a list of possible values.
- Use NOT IN to ensure a value does not match any in a set of values.
- It is particularly useful in WHERE clauses for excluding data based on a list of possible values.
- The NOT IN operator is essentially the opposite of the IN operator and offers a concise way to express multiple NOT EQUAL TO conditions.