MySQL NOT IN Clause Guide: Usage, Tips, and vs. NOT EXISTS

1. What is MySQL’s NOT IN Clause? — Making Data Extraction More Convenient with Exclusion Conditions

When performing database operations in MySQL, there are surprisingly many cases where you want to retrieve data by “excluding” specific values or conditions. For example, “display a list excluding withdrawn users” or “aggregate data excluding blacklisted IDs”—these scenarios frequently arise in business and development environments. That’s where the NOT IN clause comes into play. The NOT IN clause is a powerful SQL conditional expression that allows you to easily extract only data that does not match the specified values or the results of a subquery. Not only simple exclusions using lists, but also combining with dynamic subqueries enables various patterns of “exclusion extraction”.

However, the NOT IN clause requires attention to certain points depending on how it’s used, and there are also unexpected pitfalls. In particular, its behavior when including NULL values, performance issues in large-scale databases, differences from NOT EXISTS, and other points that are important to understand at a practical level.

In this article, we will carefully explain from the basics to advanced usage of MySQL’s NOT IN clause, including precautions and comparisons with other exclusion methods, using concrete examples. This covers useful information not only for those learning it for the first time but also for those who regularly work with SQL. Please read to the end and use it to improve your SQL skills and enhance your work efficiency.

2. Basic Syntax and Usage Examples of NOT IN

NOT IN clause is used in MySQL when you want to extract data that does not match multiple specified values. The syntax itself is simple, but it is useful in various situations in real-world scenarios. Here, we will first introduce the basic syntax and practical examples.

SELECT column_name FROM table_name WHERE column_name NOT IN (value1, value2, ...);

Exclusion Using a Simple List

For example, if you want to retrieve users whose usernames are neither “Yamada” nor “Sato”, the SQL statement would be like this.

SELECT * FROM users WHERE name NOT IN ('Yamada', 'Sato');

Executing this query will retrieve all user information except for Yamada and Sato. The exclusion list is simply values separated by commas, making it easy to write, which is a key feature.

Dynamic Exclusion Using Subqueries

NOT IN can also use a subquery inside the parentheses, not just a list. For example, it is convenient when you want to retrieve user IDs that do not meet certain conditions.

SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist WHERE is_active = 1);

In this example, we exclude user IDs that are in the active blacklist (is_active=1) from the blacklist table and retrieve the other users from the users table. The appeal of the NOT IN clause is its flexibility in handling business logic through such combinations with subqueries.

Applications with Multiple Conditions

If you want to specify exclusion conditions for multiple columns simultaneously, NOT IN is basically used for simple single-column cases, but by combining it with subqueries or joins (JOIN), it can handle complex conditions. This will be explained in detail in the advanced techniques section later.

In this way, the NOT IN clause is very convenient when you want to extract all values except those in a specified list or subquery. First, visualize the data you want to extract and try to master it from simple exclusion lists to subqueries.

3. Precautions When NULL is Mixed In

NOT IN clause, it’s surprisingly easy to overlook the behavior “when NULL values are mixed in.” This is a “pitfall” that can lead to careless mistakes not only for SQL beginners but also for experienced users.
Why is that? The judgment logic of NOT IN differs from normal comparisons, with the characteristic that “the result changes if NULL is included.”

Behavior When Including NULL

For example, suppose there is a table like the following.

-- users table
id | name
---+------
 1 | Sato
 2 | Yamada
 3 | Suzuki
 4 | Tanaka

-- blacklist table
user_id
--------
1
NULL

At this time, what happens when you execute the following SQL statement?

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);

At first glance, it seems like all users except “user_id=1” (id=2,3,4) will be retrieved. However, in reality, no rows are returned.

Why Nothing is Returned

The reason lies in “SQL’s three-valued logic (TRUE/FALSE/UNKNOWN).” When NULL is included in the NOT IN list, the comparison result becomes “UNKNOWN,” and MySQL does not include that row in the result.
In other words, since it becomes impossible to assert that any value “does not match this,” the overall SQL judgment becomes “false.”

Common Trouble Examples

Especially when using subqueries, if NULL is included in the data of blacklist or withdrawal lists, it often happens that data cannot be extracted as expected.
It easily becomes the cause of issues such as “no data comes out” or “for some reason, exclusion is not working,” so be careful.

Countermeasures and Avoidance Methods

To prevent problems caused by mixed NULLs, it is important to exclude NULL from the target list of NOT IN. Specifically, combine “IS NOT NULL” in the subquery.

SELECT * FROM users
WHERE id NOT IN (
  SELECT user_id FROM blacklist WHERE user_id IS NOT NULL
);

By doing this, even if there are NULL values in the blacklist, you can correctly extract “users that do not match.”Summary of Points

• If there is NULL in the NOT IN list, the result may return no rows at all
• When using subquery + NOT IN, don’t forget to exclude NULL with “IS NOT NULL”
• When “data cannot be retrieved,” first suspect NULL inclusion

4. NOT IN vs NOT EXISTS – Comparison of Alternatives

When specifying “exclusion conditions” in MySQL, in addition to NOT IN, NOT EXISTS is another option that is often used. Both can be used for similar purposes, but there are differences in their mechanisms, behavior, performance, and handling of NULL values. In this chapter, we will explain how to choose between NOT IN and NOT EXISTS, and their respective advantages and disadvantages.

Comparison of Basic Syntax

SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist WHERE user_id IS NOT NULL);

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.user_id = u.id
);

Both are queries that extract users not registered in the blacklist.

Robustness Against NULL Values

NOT IN

• If NULL is mixed in the subquery side or list, it may not behave as intended (results may be 0 rows)
• As a countermeasure, the “IS NOT NULL” condition is necessary

NOT EXISTS

• Even if NULL is included in the subquery results, it works correctly
• Basically unaffected by NULL values, so higher safety

Differences in Performance

The optimal way depends on the data volume and table structure, but generally…

• For small-scale data or fixed lists, NOT IN is fine
• When the subquery is large or complex conditions are involved, NOT EXISTS or LEFT JOIN often performs better

Especially as the number of blacklist entries increases, cases where NOT EXISTS operates more efficiently increase. It depends on the MySQL version and index design, but NOT EXISTS performs “existence checks for the subquery for each row,” so it is very fast when indexes are effective.

Guidelines for Choosing Between Them

• When there is a possibility of NULL values being mixed in
  → Recommend NOT EXISTS
• For fixed lists or simple value exclusions only
  → NOT IN is fine
• When prioritizing performance the most
  → Check the execution plan with EXPLAIN and choose according to the situation (consider JOIN or NOT EXISTS)

Sample Cases

Example of Issue with NOT IN

-- When NULL is included in blacklist.user_id
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist);
-- Results may be 0 rows

Safe Exclusion Example with NOT EXISTS

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.user_id = u.id
);
-- Can extract correctly regardless of the presence of NULL

Summary

• NOT IN is simple but weak to NULL contamination
• NOT EXISTS is strong against NULL and commonly used in practice
• Choose which to use based on “data content” and “desired performance”

5. Performance Considerations

When handling large amounts of data in SQL, query performance is extremely important. When using NOT IN or NOT EXISTS, there can be significant differences in processing speed depending on the conditions and data volume. This chapter explains the impact on performance, precautions, and optimization tips, focusing on the NOT IN clause.

Characteristics of NOT IN Clause Performance

NOT IN is a mechanism that extracts records if there is no matching data in the specified list or subquery. It operates quickly for small lists or tables, but processing can slow down in cases like the following.

• When the number of data rows on the subquery side is large
• When no index is set on the exclusion target column
• When NULL values are mixed in the subquery

Especially, if there are tens of thousands or hundreds of thousands of data rows in the subquery, and no index is set, MySQL performs a full comparison, which can make it extremely slow.

The Importance of Utilizing Indexes

By setting a index on the exclusion target column (for example, user_id), MySQL can perform comparisons and extractions efficiently. Actively set indexes on columns used in subqueries or joins (JOIN).

CREATE INDEX idx_blacklist_user_id ON blacklist(user_id);

Adding an index like this can significantly improve the performance of NOT IN and NOT EXISTS.

Performance Comparison Between NOT IN and NOT EXISTS

• For small fixed lists: NOT IN is faster
• For large subqueries: Using NOT EXISTS or LEFT JOIN is more efficient

Especially in MySQL, the execution plan (EXPLAIN results) varies by version and table design, so it’s important to actually test for performance optimization.

Checking the Execution Plan with EXPLAIN

To know which query is faster, use MySQL’s EXPLAIN command.

EXPLAIN SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist WHERE user_id IS NOT NULL);

This allows you to check information directly related to performance, such as which indexes are being used and which tables are undergoing full scans.

Optimization Examples for Large-Scale Data

• As needed, store data in a temporary table to reduce the burden on the subquery
• If it’s still slow, utilize batch processing or caching
• Try rewriting with LEFT JOIN ... IS NULL (it can speed up depending on the situation)

Summary of Points

• NOT IN tends to be slow when the subquery has many rows or no index
• Performance can be greatly improved by index design and query review
• Consider NOT EXISTS and LEFT JOIN as options and verify their effects with EXPLAIN

In practical or production environments, select the optimal query considering the data scale and usage frequency.

6. Common Usage Patterns and Advanced Techniques

NOT IN clause is not only for simple exclusion processing but can also enable more flexible data extraction when applied creatively. Here, we introduce commonly used patterns in the field and useful advanced techniques.

Exclusion Using Multiple Columns (Excluding Composite Keys)

NOT IN is basically used for a single column, but there are also needs to exclude based on combinations of two or more columns. In such cases, NOT EXISTS or LEFT JOIN with composite conditions is appropriate.

Example: Excluding specific combinations of customer ID and product ID in the orders table

SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b
  WHERE b.customer_id = o.customer_id
    AND b.product_id = o.product_id
);

This way, you can exclude combinations of “customer ID × product ID” registered in the blacklist all at once.

Partial Match (Wildcard) Exclusion: Combining with NOT LIKE

NOT IN can only be used for exact value matches, but NOT LIKE is convenient when you want to exclude specific string patterns. For example, when you want to exclude users whose email addresses start with “test@”.

SELECT * FROM users WHERE email NOT LIKE 'test@%';

If you want to exclude multiple patterns at the same time, connect the conditions with AND.

SELECT * FROM users
WHERE email NOT LIKE 'test@%'
  AND email NOT LIKE 'sample@%';

Tips for When the List is Large

If you directly list many values (hundreds to thousands of items) in NOT IN, the readability and performance of SQL will decrease.
In this case, the key is to use temporary tables or subqueries to improve readability and maintainability.

-- Example: Saving the exclusion list in the blacklist table
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist WHERE user_id IS NOT NULL);

Exclusion Combined with Aggregate Functions

Subqueries for aggregation results can also effectively use NOT IN when excluding data under specific conditions.

Example: Extracting customers who have not placed orders this month

SELECT * FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders
  WHERE order_date >= '2025-06-01'
    AND order_date < '2025-07-01'
);

Example of Using JOIN Instead of Subquery

In some cases, you can achieve the same result with LEFT JOIN + IS NULL.
Considering performance and readability, choose the optimal method.

SELECT u.*
FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;

This method is particularly recommended when you are concerned about subquery performance or when indexes work effectively.Summary of Points

• NOT EXISTS or JOIN is convenient for excluding multiple columns
• Combine with NOT LIKE for partial match exclusion
• Managing lists with subqueries or temporary tables makes operations easier
• JOIN + IS NULL can also improve performance

7. Frequently Asked Questions (FAQ)

This section summarizes common questions and pitfalls regarding MySQL’s NOT IN clause that often arise in practice. Focusing on queries that are commonly searched, it provides simple and practical answers.Q1. What is the difference between NOT IN and IN?A. IN retrieves data that matches any item in the specified list, whereas NOT IN is used to retrieve only data that does not match any item in the specified list. The usage is almost the same, but choose NOT IN when you want to apply exclusion conditions.Q2. What happens if there is a NULL value in NOT IN?A.
If NULL is mixed into the list or subquery, NOT IN may return no rows or produce unexpected results. It’s safe to always exclude NULLs using “IS NOT NULL” or similar before using it.Q3. How to choose between NOT IN and NOT EXISTS?A.

• When there is a possibility of NULL values or using subqueries, NOT EXISTS is more reliable.
• For fixed lists or simple value exclusions, NOT IN is fine.
• Depending on the execution plan or data volume, performance may differ, so choose based on the situation.

Q4. Sometimes using NOT IN makes the query slow. What are the countermeasures?A.

• Set an index on the column to exclude
• Reduce the number of rows in the subquery or organize the data into a temporary table beforehand
• In some cases, consider rewriting to NOT EXISTS or LEFT JOIN ... IS NULL
• It’s effective to use EXPLAIN to check the execution plan and identify bottlenecks

Q5. What if I want to exclude based on multiple columns?A. Since NOT IN is dedicated to single columns, for excluding based on two or more columns in combination, use NOT EXISTS or LEFT JOIN. Implement the exclusion process by combining conditions for multiple columns within the subquery.Q6. If the subquery result is large, what points should I be careful about?A.
If the number of results in the subquery is large, NOT IN tends to degrade performance. It’s recommended to plan ahead with strategies like utilizing indexes, splitting into temporary tables, and keeping the subquery as small as possible.Q7. If I can’t get the intended results no matter what, what should I review?A.

• Check if NULL values are mixed in
• Execute the subquery individually to verify if the results are as expected
• Check for errors in WHERE conditions or JOIN connections
• Behavior may differ depending on the database version or settings, so refer to the official documentation as well

8. Summary

The NOT IN clause in MySQL is a very useful syntax for efficiently extracting data that does not meet specific conditions. It can be used in various scenarios, from simple list-based exclusions to flexible data extraction combined with subqueries.

On the other hand, the NOT IN clause has points to be aware of in practice, such as “precautions when NULL values are included” and “performance degradation with large-scale data.” In particular, unexpected exclusion of all records due to NULL inclusion, or slowdown when the number of subqueries is large—these require attention not only from SQL beginners but also from experienced users.

Furthermore, by understanding alternatives to NOT IN such as NOT EXISTS and LEFT JOIN ... IS NULL, you can write safer and more efficient SQL.
Please select the optimal method according to the purpose and data scale.Review of Key Points

• NOT IN is effective for simple exclusion conditions
• Always be cautious of the impact of NULL values (make it a habit to use IS NOT NULL in combination)
• If performance is a concern, consider index design, NOT EXISTS, and JOIN as options
• Always check the effectiveness with the execution plan (EXPLAIN)

While avoiding the “pitfalls” of SQL, please make use of the content of this article in your daily work and learning to enable smart data extraction.