MySQL NOT IN() function
NOT IN() function
The 'NOT IN' operator in MySQL is used to filter rows based on the absence of specified values in a list. It ensures that the value of an expression does not match any value within the provided set. This is particularly useful for exclusion queries where you want to omit rows that contain certain values in a specified column.
Performance Considerations of NOT IN:
The 'NOT IN' operator can be less efficient than other exclusion methods, especially with large datasets or subqueries. For instance, using 'NOT EXISTS' or left joins with a 'NULL' check might offer better performance in certain scenarios. It's essential to understand the execution plan and choose the most efficient approach based on the specific use case.
Syntax:
expression NOT IN (value1, value2, ..., valueN)
- expression: The column or value to be compared.
- value1, value2, ..., valueN: The list of values to be checked against the expression. The 'expression' must not be equal to any of these values for the condition to hold true.
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 book_mast 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:
-- This SQL query selects the name of the book, date of publication,
--publication language, number of pages, and book price
-- Explanation: The query retrieves information from the 'book_mast' table,
--filtering out books published in languages other than
--English and having a price that is not 100 or 200.
SELECT book_name, dt_of_pub, pub_lang, no_page, book_price -- Selecting
--specific columns book_name,
--dt_of_pub, pub_lang, no_page, and book_price from the 'book_mast' table
FROM book_mast -- Specifying the table 'book_mast' from which to retrieve the data
WHERE pub_lang != "English" -- Filtering the rows based on the publication
--language column, selecting rows where the language is not equal to "English"
AND book_price NOT IN (100, 200); -- Further filtering based on the book price
--column, selecting rows where the price is not 100 or 200
Explanation:
- The SELECT statement specifies the columns to be retrieved from the 'book_mast' table: book_name, dt_of_pub, pub_lang, no_page, and book_price.
- The FROM clause specifies the table from which the data is to be retrieved, which is 'book_mast'.
- The WHERE clause is used to filter the rows based on multiple conditions.
- The comparison operator != (not equal to) is used to select rows where the publication language is not equal to "English".
- The NOT IN operator is used to select rows where the book price is not in the specified list (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:
-- This SQL query selects the name of the book, date of publication,
--publication language, number of pages, and book price
-- Explanation: The query retrieves information from the 'book_mast'
--table, filtering out books published in English and German.
SELECT book_name, dt_of_pub, pub_lang, no_page, book_price -- Selecting
--specific columns book_name, dt_of_pub, pub_lang, no_page,
--and book_price from the 'book_mast' table
FROM book_mast -- Specifying the table 'book_mast' from which
--to retrieve the data
WHERE pub_lang NOT IN ("English", "German"); -- Filtering the rows based
--on the publication language column, selecting rows where the
--language is not English or German
Explanation:
- The SELECT statement is used to specify the columns to be retrieved from the 'book_mast' table: book_name, dt_of_pub, pub_lang, no_page, and book_price.
- The FROM clause specifies the table from which the data is to be retrieved, which is 'book_mast'.
- The WHERE clause is used to filter the rows based on a condition.
- The NOT IN operator is used to specify a list of values for which rows should be excluded. In this case, it excludes rows where the publication language is either "English" or "German".
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:
Sample table: employees
-----------+-----------+----------+----------+--------+----------+-------------+
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
-----------+-----------+----------+----------+--------+----------+-------------+
The following query efficiently retrieves all employee records that do not fall into two job categories such as 'PU_CLERK ' or 'ST_CLERK '.
Code:
-- Selecting all columns from the 'employees' table
SELECT *
FROM employees
-- Filtering the rows to exclude those where the job_id is 'PU_CLERK ' or 'ST_CLERK '
WHERE job_id NOT IN ('PU_CLERK ', 'ST_CLERK ');
Explanation:
- SELECT: This part of the query is used to retrieve all columns from the resulting rows of the query. The asterisk (*) is a wildcard that means "select all columns."
- FROM employees: This specifies the source table from which the data is to be fetched. In this case, the data is being retrieved from the employees table.
- WHERE job_id NOT IN ('PU_CLERK ', 'ST_CLERK '):
- The WHERE clause filters the rows based on a condition.
- job_id is the column being evaluated against a list of values.
- NOT IN is an operator that checks if the value of job_id is not within the provided list.
- The list ('PU_CLERK ', 'ST_CLERK ') contains two specific job IDs. The query will exclude rows where job_id is either 'PU_CLERK ' or 'ST_CLERK '.
Output:
-----------+-----------+----------+----------+--------+----------+-------------+
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
-----------+-----------+----------+----------+--------+----------+-------------+
Example:
If you want to generate a report of all orders, excluding those placed by customers who are in a blacklist due to fraudulent activities or other issues.
Code:
-- Selecting all columns from the 'orders' table
SELECT *
FROM orders
-- Filtering the rows to exclude those where the customer_id is present in the 'blacklist' table
WHERE customer_id NOT IN
( -- Subquery to select customer_id values from the 'blacklist' table
SELECT customer_id
FROM blacklist
);
Explanation:
- SELECT: This part of the query selects all columns from the rows that meet the condition specified in the WHERE clause. The asterisk (*) is a wildcard character that means "select all columns."
- FROM orders: This specifies the source table from which the data will be retrieved. In this case, the orders table is the source of the data.
- WHERE customer_id NOT IN:
- The WHERE clause is used to filter the rows in the orders table based on the condition that follows.
- customer_id NOT IN checks if the customer_id from the orders table is not present in the list of customer IDs returned by the subquery.
- This means only rows where the customer_id does not match any of the IDs in the subquery result will be included in the final result.
- (SELECT customer_id FROM blacklist):
- This is a subquery that retrieves a list of customer_id values from the blacklist table.
- The subquery provides a set of customer IDs that are to be excluded from the orders table results.
- Essentially, it’s creating a list of customer_id values that are blacklisted.
Comparing NOT IN with Other Operators
Code:
SELECT *
FROM orders o
WHERE NOT EXISTS
(SELECT 1 FROM blacklist b WHERE b.customer_id = o.customer_id);
Code:
SELECT *
FROM products
WHERE price <> ALL (SELECT discounted_price FROM discounts);
Code:
SELECT
* FROM customers
WHERE email NOT LIKE '%@gmail.com';
Relational Algebra Expression:
Relational Algebra Tree:
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:
-- This SQL query selects all columns from the 'book_mast' table
-- Explanation: The query retrieves all rows from the 'book_mast'
--table where the publisher ID does not exist in the 'publisher' table.
SELECT * -- Selecting all columns from the 'book_mast' table
FROM book_mast -- Specifying the table 'book_mast' from which to
--retrieve the data
WHERE pub_id NOT IN ( -- Filtering the rows based on the publisher ID,
--selecting rows where the publisher ID is not in the subquery result
SELECT pub_id -- Selecting the publisher ID from the 'publisher' table
FROM publisher -- Specifying the table 'publisher' from which to
--retrieve the publisher ID
);
Explanation:
- The SELECT * statement is used to retrieve all columns from the 'book_mast' table.
- The FROM clause specifies the table from which the data is to be retrieved, which is 'book_mast'.
- The WHERE clause is used to filter the rows based on a condition.
- The NOT IN operator is used to exclude rows where the publisher ID exists in the subquery result.
- The subquery SELECT pub_id FROM publisher retrieves all publisher IDs from the 'publisher' table.
- The main query selects all rows from 'book_mast' where the pub_id is not present in the list of pub_ids returned by the subquery.
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)
Frequently Asked Questions (FAQ) - MySQL NOT IN Function
1. What is the NOT IN Operator in MySQL?
The NOT IN operator is used to filter rows by excluding those that match any value in a specified list. It ensures that a column's value does not appear in the provided set of values.
2. How does the MySQL NOT IN Operator work?
When you use the NOT IN operator, it compares a specified column against a list of values. Rows are included in the result only if the column's value does not match any value in the list.
3. What are the performance considerations for using MySQL NOT IN?
The NOT IN operator can be less efficient with large datasets or complex subqueries. Alternatives like NOT EXISTS or left joins with a NULL check might offer better performance. It's important to analyze the execution plan and choose the most efficient method for your specific use case.
4. Can you explain the basic use of MySQL NOT IN?
The NOT IN operator is typically used in the WHERE clause of a query to exclude rows where a column’s value is within a specified list of values.
5. How does MySQL NOT IN differ from other exclusion operators like MySQL NOT EXISTS?
- NOT EXISTS: Evaluates if no rows are returned by a subquery, often more efficient for subqueries with complex conditions.
- <> ALL: Similar to NOT IN, but usually applied in scenarios involving subqueries rather than static lists.
6. What are some common alternatives to the MySQL NOT IN operator?
answer
- NOT EXISTS: Often used when working with subqueries, especially when you want to check for non-existence of records.
- Left Join with NULL check: A left join that filters out matching rows using a WHERE clause that checks for NULL values.
7. When should we use the MySQL NOT IN operator?
Use the NOT IN operator when we need to exclude rows that match any value in a specific list and when working with relatively small sets of values. For larger datasets or complex conditions, consider alternatives for better performance.
8. Are there any potential pitfalls with using MySQL NOT IN?
Yes, one potential issue is that if any value in the list or subquery returns NULL, the NOT IN condition can fail to match as expected. It’s important to handle NULL values carefully when using NOT IN.
9. How can we ensure the best performance with MySQL NOT IN?
To ensure optimal performance:
- Avoid using NOT IN with large subqueries; consider NOT EXISTS instead.
- Make sure the list or subquery used with NOT IN is indexed, if possible.
- Analyze and optimize your query execution plan.
Slideshow of MySQL Comparison Function and Operators
Previous: NOT EQUAL OPERATOR(<>,!=)
Next: NOT LIKE