Overview
In this tutorial, we will understand MySQL NOT IN Operator. We can use this operator in the SELECT
WHERE clause to find mismatches of the values ( values that are not ) in the specified set of values. The NOT IN operator is the exact opposite of the IN operator.
Syntax
The general syntax for the operator usage is as follows:
mysql> SELECT column_list
FROM table_name
WHERE search_column NOT IN (set of values);
Example
In this example, we will use the Country and CountryLanguage tables from the world MySQL table.
For example, a NOT IN subquery that identifies countries for which no languages are listed in the CountryLanguage table:
mysql> SELECT Name FROM Country WHERE Code NOT IN
-> ( SELECT CountryCode FROM CountryLanguage);
+———————————————-+
| Name |
+———————————————-+
| Antarctica |
| French Southern territories |
| Bouvet Island |
| Heard Island and McDonald Islands |
| British Indian Ocean Territory |
| South Georgia and the South Sandwich Islands |
+———————————————-+
6 rows in set (0.00 sec)
This query can be rewritten as an outer join using a LEFT JOIN as follows:
mysql> SELECT Name FROM Country
LEFT JOIN CountryLanguage ON Code =CountryCode
WHERE CountryCode IS NULL;
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: