MySQL NOT IN Operator
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:
