MySQL WHERE IN - NOT IN

MySQL WHERE IN and NOT IN clauses are used to filter query results based on a list of specific values or a subquery.

The WHERE IN clause allows you to specify a list of values to be matched, and the query will return all rows where the specified column value matches one of the values in the list.

On the other hand, the WHERE NOT IN clause returns all rows where the specified column value does not match any of the values in the list.

Initial situation:

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

WHERE example:

mysql> SELECT * FROm friends WHERE Lastname IN ('Gatez', 'Snow');
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

This instruction selects all rows from the friends table where the Lastname column matches either 'Gatez' or 'Snow', using the IN operator.

 It returns a table with the columns id, Name, Lastname, and Telephone. In this case, two rows are matched and returned in the result set. 

If in requests is something that is not in a table, like "XXX":

mysql> SELECT * FROm friends WHERE Lastname IN ('Gatez', 'XXX');
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

Usage of OR in combination with WHERE:

mysql> SELECT * FROm friends WHERE Lastname='Gatez' OR Lastname='Snow';
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

NOT IN example:

mysql> SELECT * FROM friends WHERE Telephone NOT IN (555666, 666777);
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

This MySQL query selects all rows from the friends table where the Telephone column is not equal to 555666 or 666777. The NOT IN operator is used to exclude specific values from the result set. 

We can target specific columns:

mysql> SELECT id, Name, Telephone FROM friends WHERE Telephone NOT IN (555666, 666777);
+----+------+-----------+
| id | Name | Telephone |
+----+------+-----------+
| 15 | Ana  |    111222 |
| 18 | John |    999888 |
+----+------+-----------+
2 rows in set (0.00 sec)

mysql>