MySQL NOT IN() function
NOT IN() function
MySQL NOT IN() makes sure that the expression proceeded does not have any of the values present in the arguments.
Syntax:
expr NOT IN (value,...)
MySQL Version: 8.0
Example: MySQL NOT IN() function
Sample table: book_mast
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+ | book_id | book_name | isbn_no | cate_id | aut_id | pub_id | dt_of_pub | pub_lang | no_page | book_price | +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+ | BK001 | Introduction to Electrodynamics | 0000979001 | CA001 | AUT001 | P003 | 2001-05-08 | English | 201 | 85.00 | | BK002 | Understanding of Steel Construction | 0000979002 | CA002 | AUT002 | P001 | 2003-07-15 | English | 300 | 105.50 | | BK003 | Guide to Networking | 0000979003 | CA003 | AUT003 | P002 | 2002-09-10 | Hindi | 510 | 200.00 | | BK004 | Transfer of Heat and Mass | 0000979004 | CA002 | AUT004 | P004 | 2004-02-16 | English | 600 | 250.00 | | BK005 | Conceptual Physics | 0000979005 | CA001 | AUT005 | P006 | 2003-07-16 | NULL | 345 | 145.00 | | BK006 | Fundamentals of Heat | 0000979006 | CA001 | AUT006 | P005 | 2003-08-10 | German | 247 | 112.00 | | BK007 | Advanced 3d Graphics | 0000979007 | CA003 | AUT007 | P002 | 2004-02-16 | Hindi | 165 | 56.00 | | BK008 | Human Anatomy | 0000979008 | CA005 | AUT008 | P006 | 2001-05-17 | German | 88 | 50.50 | | BK009 | Mental Health Nursing | 0000979009 | CA005 | AUT009 | P007 | 2004-02-10 | English | 350 | 145.00 | | BK010 | Fundamentals of Thermodynamics | 0000979010 | CA002 | AUT010 | P007 | 2002-10-14 | English | 400 | 225.00 | | BK011 | The Experimental Analysis of Cat | 0000979011 | CA004 | AUT011 | P005 | 2007-06-09 | French | 225 | 95.00 | | BK012 | The Nature of World | 0000979012 | CA004 | AUT005 | P008 | 2005-12-20 | English | 350 | 88.00 | | BK013 | Environment a Sustainable Future | 0000979013 | CA004 | AUT012 | P001 | 2003-10-27 | German | 165 | 100.00 | | BK014 | Concepts in Health | 0000979014 | CA005 | AUT013 | P004 | 2001-08-25 | NULL | 320 | 180.00 | | BK015 | Anatomy & Physiology | 0000979015 | CA005 | AUT014 | P008 | 2000-10-10 | Hindi | 225 | 135.00 | | BK016 | Networks and Telecommunications | 00009790_16 | CA003 | AUT015 | P003 | 2002-01-01 | French | 95 | 45.00 | | BK1234 | ASDFASD | ASDF | ASDF | ASDF | P010 | 2001-10-10 | ENGLISH | 235 | 234.00 | +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+ 17 rows in set (0.03 sec)
Sample table: publisher
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P002 | BPP Publication | Mumbai | India | New Delhi | 10 | 1985-10-01 | | P003 | New Harrold Publication | Adelaide | Australia | Sydney | 6 | 1975-09-05 | | P004 | Ultra Press Inc. | London | UK | London | 8 | 1948-07-10 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | | P007 | Pieterson Grp. of Publishers | Cambridge | UK | London | 6 | 1950-07-15 | | P008 | Novel Publisher Ltd. | New Delhi | India | Bangalore | 10 | 2000-01-01 | | P009 | ASDFASD | ASDF | ASD | ASDF | 1 | 0000-00-00 | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+ 9 rows in set (0.04 sec)
If you want to fetch the rows from the table <i>book_mast</i> which contain such books, not written in English and the price of the books are not 100 or 200, the following statement can be used.
Code:
SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
FROM book_mast
WHERE pub_lang!="English"
AND book_price NOT IN (100,200);
Output:
+----------------------------------+------------+----------+---------+------------+ | book_name | dt_of_pub | pub_lang | no_page | book_price | +----------------------------------+------------+----------+---------+------------+ | Fundamentals of Heat | 2003-08-10 | German | 247 | 112.00 | | Advanced 3d Graphics | 2004-02-16 | Hindi | 165 | 56.00 | | Human Anatomy | 2001-05-17 | German | 88 | 50.50 | | The Experimental Analysis of Cat | 2007-06-09 | French | 225 | 95.00 | | Anatomy & Physiology | 2000-10-10 | Hindi | 225 | 135.00 | | Networks and Telecommunications | 2002-01-01 | French | 95 | 45.00 | +----------------------------------+------------+----------+---------+------------+ 6 rows in set (0.00 sec)
Example:
If you want to fetch the rows from the table book_mast which contain books not written in English or German, the following sql statement can be used.
Code:
SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
FROM book_mast
WHERE pub_lang NOT IN("English","German");
Relational Algebra Expression:

Relational Algebra Tree:

Output:
+----------------------------------+------------+----------+---------+------------+ | book_name | dt_of_pub | pub_lang | no_page | book_price | +----------------------------------+------------+----------+---------+------------+ | Guide to Networking | 2002-09-10 | Hindi | 510 | 200.00 | | Advanced 3d Graphics | 2004-02-16 | Hindi | 165 | 56.00 | | The Experimental Analysis of Cat | 2007-06-09 | French | 225 | 95.00 | | Anatomy & Physiology | 2000-10-10 | Hindi | 225 | 135.00 | | Networks and Telecommunications | 2002-01-01 | French | 95 | 45.00 | +----------------------------------+------------+----------+---------+------------+ 5 rows in set (0.00 sec)
Example of MySQL NOT IN using two tables
If you want to fetch those rows from the table book_mast which does not contain those pub_id's which are not exist in publisher table, the following sql can be used.
Code:
SELECT * FROM book_mast
WHERE pub_id NOT IN(
SELECT pub_id FROM publisher);
Output:
+---------+-----------+---------+---------+--------+--------+------------+----------+---------+------------+ | book_id | book_name | isbn_no | cate_id | aut_id | pub_id | dt_of_pub | pub_lang | no_page | book_price | +---------+-----------+---------+---------+--------+--------+------------+----------+---------+------------+ | BK1234 | ASDFASD | ASDF | ASDF | ASDF | P010 | 2001-10-10 | ENGLISH | 235 | 234.00 | +---------+-----------+---------+---------+--------+--------+------------+----------+---------+------------+ 1 row in set (0.06 sec)
Slideshow of MySQL Comparison Function and Operators
Previous: NOT EQUAL OPERATOR(<>,!=)
Next: NOT LIKE
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join