IN and NOT IN operators in MySQL - Deep Dive

Overview - IN and NOT IN operators

What is it?

IN and NOT IN are SQL operators used to check if a value matches any value in a list or set. IN returns true if the value is found in the list, while NOT IN returns true if the value is not found. They simplify queries by replacing multiple OR conditions with a cleaner syntax. These operators help filter data based on multiple possible values efficiently.

Why it matters

Without IN and NOT IN, filtering data for multiple values would require writing many OR conditions, making queries long, hard to read, and error-prone. This would slow down development and increase mistakes. IN and NOT IN make queries simpler, faster to write, and easier to understand, improving productivity and reducing bugs.

Where it fits

Before learning IN and NOT IN, you should understand basic SQL SELECT statements and WHERE clauses. After mastering these operators, you can learn about JOINs, subqueries, and advanced filtering techniques to handle more complex data retrieval.

Mental Model

Core Idea

IN and NOT IN operators check if a value is inside or outside a list of values, acting like a quick membership test in SQL.

Think of it like...

Imagine you have a guest list for a party. IN is like checking if a person's name is on the guest list to allow entry. NOT IN is like checking if a person is NOT on the list to deny entry.

┌───────────────┐
│   Value to    │
│    Check      │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│          List of Values      │
│  (e.g., 'apple', 'banana')  │
└────────────┬────────────────┘
             │
      ┌──────▼───────┐       
      │   IN Operator │  Returns TRUE if value is in list
      └──────────────┘       

      ┌──────▼────────┐      
      │  NOT IN Operator│ Returns TRUE if value is NOT in list
      └───────────────┘

Build-Up - 6 Steps

1

Foundation

Basic use of IN operator

Concept: Learn how to use the IN operator to filter rows matching any value in a list.

Suppose you have a table 'fruits' with a column 'name'. To find rows where the fruit name is either 'apple', 'banana', or 'cherry', you write: SELECT * FROM fruits WHERE name IN ('apple', 'banana', 'cherry'); This returns all rows where 'name' matches any of the listed fruits.

Result

The query returns all rows with 'name' equal to 'apple', 'banana', or 'cherry'.

Understanding IN lets you replace multiple OR conditions with a cleaner, shorter syntax that is easier to read and write.

2

Foundation

Basic use of NOT IN operator

Concept: Learn how to use NOT IN to exclude rows matching any value in a list.

Using the same 'fruits' table, to find fruits that are NOT 'apple', 'banana', or 'cherry', write: SELECT * FROM fruits WHERE name NOT IN ('apple', 'banana', 'cherry'); This returns rows where 'name' is none of those fruits.

Result

The query returns all rows where 'name' is not 'apple', 'banana', or 'cherry'.

NOT IN helps you exclude multiple values easily without writing many AND conditions.

3

Intermediate

IN operator with subqueries

🤔Before reading on: do you think IN can only check against fixed lists, or can it also check against results from another query? Commit to your answer.

Concept: IN can check if a value exists in the result set of another query, not just a fixed list.

You can use a subquery inside IN to filter based on dynamic data. For example, to find customers who ordered products from a specific category: SELECT * FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE product_category = 'Books' ); This finds customers who have orders in the 'Books' category.

Result

The query returns customers who ordered products in the 'Books' category.

Knowing IN works with subqueries unlocks powerful dynamic filtering based on related data.

4

Intermediate

NOT IN with NULL values caution

🤔Before reading on: do you think NOT IN behaves the same when the list contains NULL values? Commit to your answer.

Concept: NOT IN behaves unexpectedly if the list contains NULL because NULL means unknown, causing the condition to fail.

If you run: SELECT * FROM fruits WHERE name NOT IN ('apple', NULL, 'banana'); No rows may be returned because NULL causes the comparison to be unknown, and SQL treats unknown as false in WHERE. To avoid this, use NOT EXISTS or filter out NULLs explicitly.

Result

The query returns zero rows or unexpected results due to NULL in the list.

Understanding NULL's effect on NOT IN prevents subtle bugs and incorrect query results.

5

Advanced

Performance considerations of IN vs OR

🤔Before reading on: do you think IN is always faster than multiple OR conditions? Commit to your answer.

Concept: IN often performs better than multiple ORs, but performance depends on database engine and indexes.

Using IN with many values is usually optimized internally by the database, making queries faster and easier to maintain than many OR conditions. However, very large IN lists can slow queries. Sometimes JOINs or EXISTS are better for performance. Example: SELECT * FROM fruits WHERE name IN ('apple', 'banana', 'cherry'); is usually faster and cleaner than: SELECT * FROM fruits WHERE name = 'apple' OR name = 'banana' OR name = 'cherry';

Result

IN queries are generally more efficient and readable than multiple ORs.

Knowing when IN improves performance helps write efficient and maintainable queries.

6

Expert

Internal handling of IN with subqueries

🤔Before reading on: do you think the database runs the subquery once or multiple times when using IN with a subquery? Commit to your answer.

Concept: Databases optimize IN with subqueries by running the subquery once and caching results or transforming it into joins.

When you write: SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders); the database engine often executes the subquery once, stores the results in memory, and then checks each customer_id against this set. Some engines rewrite this as a JOIN internally for better performance. This optimization avoids running the subquery repeatedly for each row.

Result

Queries with IN subqueries run efficiently due to internal caching and query rewriting.

Understanding query optimization behind IN with subqueries helps write performant SQL and troubleshoot slow queries.

Under the Hood

IN and NOT IN operators work by comparing a value against a set of values. For fixed lists, the database checks if the value matches any item in the list. For subqueries, the database executes the subquery first, stores the results in a temporary structure, then tests each row's value against this set. NOT IN adds complexity because if any value in the list is NULL, the comparison becomes unknown, causing the condition to fail. Internally, the database uses indexes and query optimization techniques like hash lookups or merge joins to speed up these checks.

Why designed this way?

IN and NOT IN were designed to simplify writing multiple OR conditions and to allow filtering based on dynamic sets from subqueries. Early SQL required verbose OR chains, which were error-prone and inefficient. The design balances readability and performance. Handling NULLs in NOT IN is tricky due to SQL's three-valued logic, but this strictness avoids incorrect assumptions about unknown data. Alternatives like EXISTS were introduced to handle NULLs more safely.

┌───────────────┐
│   Query Row   │
│   Value X     │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│   IN List or Subquery Set   │
│  (Executed once if subquery)│
└────────────┬────────────────┘
             │
      ┌──────▼───────┐       
      │  Comparison  │  Checks if X is in the set
      └──────┬───────┘       
             │
      ┌──────▼───────┐       
      │  TRUE or FALSE│      
      └──────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does NOT IN return true if the list contains NULL values? Commit to yes or no.

Common Belief: NOT IN works the same regardless of NULLs in the list and returns true if the value is not found.

Reality: If the list contains NULL, NOT IN returns no rows because NULL causes the comparison to be unknown, which SQL treats as false.

Why it matters: This causes queries to return empty results unexpectedly, leading to confusion and bugs in data filtering.

Quick: Is IN always faster than multiple OR conditions? Commit to yes or no.

Common Belief: IN is always faster than writing multiple OR conditions.

Reality: IN is usually faster and cleaner, but performance depends on database engine, indexes, and query complexity. Sometimes OR performs similarly or better.

Why it matters: Assuming IN is always faster can lead to ignoring query plans and missing better optimization opportunities.

Quick: Does the database run the subquery inside IN for every row? Commit to yes or no.

Common Belief: The subquery inside IN runs once per row in the outer query.

Reality: Most databases execute the subquery once, cache results, or rewrite the query as a join for efficiency.

Why it matters: Misunderstanding this can cause unnecessary query rewrites or performance fears.

Quick: Can IN be used with columns of different data types without issues? Commit to yes or no.

Common Belief: IN works fine even if the value and list items have different data types.

Reality: Data type mismatches can cause errors or unexpected results; implicit conversions may happen but are not reliable.

Why it matters: Ignoring data types can cause bugs or slow queries due to implicit conversions.

Expert Zone

1

IN with subqueries can be internally transformed into semi-joins or hash lookups, which affects performance and execution plans.

2

NOT IN's behavior with NULLs is a common source of subtle bugs; using NOT EXISTS is often safer for exclusion queries.

3

Large IN lists may cause query parsing overhead; in such cases, temporary tables or JOINs can be more efficient.

When NOT to use

Avoid NOT IN when the list or subquery can contain NULL values; use NOT EXISTS instead. For very large lists, avoid IN and consider JOINs or temporary tables. When filtering complex conditions, EXISTS or JOINs may be clearer and more performant.

Production Patterns

IN is commonly used for filtering by multiple fixed values or subquery results, such as filtering users by roles or products by categories. NOT IN is used for exclusion but often replaced by NOT EXISTS to handle NULLs safely. Developers monitor query plans to decide between IN, EXISTS, and JOINs for best performance.

Connections

Set Theory

IN and NOT IN correspond to membership and non-membership in sets.

Understanding SQL IN as set membership helps grasp filtering logic and parallels mathematical set operations.

Boolean Logic

IN and NOT IN simplify multiple OR and AND conditions into concise expressions.

Recognizing how IN condenses OR chains clarifies query simplification and logical equivalences.

Access Control Lists (ACLs)

IN operator is like checking if a user is in an allowed list for permissions.

Knowing IN's role in filtering helps understand how systems check membership for access rights.

Common Pitfalls

#1 Using NOT IN with a list containing NULL values causes no rows to be returned.

Wrong approach:

SELECT * FROM fruits WHERE name NOT IN ('apple', NULL, 'banana');

Correct approach:

SELECT * FROM fruits WHERE name NOT IN ('apple', 'banana') AND name IS NOT NULL;

Root cause:

NULL in the list causes the NOT IN condition to evaluate as unknown, which SQL treats as false, filtering out all rows.

#2 Using IN with mismatched data types leads to errors or unexpected results.

Wrong approach:

SELECT * FROM fruits WHERE name IN (1, 2, 3);

Correct approach:

SELECT * FROM fruits WHERE id IN (1, 2, 3);

Root cause:

Comparing strings to numbers without proper data types causes implicit conversion errors or no matches.

#3 Writing multiple OR conditions instead of using IN makes queries verbose and error-prone.

Wrong approach:

SELECT * FROM fruits WHERE name = 'apple' OR name = 'banana' OR name = 'cherry';

Correct approach:

SELECT * FROM fruits WHERE name IN ('apple', 'banana', 'cherry');

Root cause:

Not knowing IN operator leads to unnecessarily long and harder to maintain queries.

Key Takeaways

IN and NOT IN operators simplify checking if a value is inside or outside a list of values in SQL queries.

IN can work with fixed lists or subqueries, enabling dynamic and powerful filtering.

NOT IN behaves unexpectedly if the list contains NULL values; use caution or prefer NOT EXISTS for exclusions.

IN usually improves query readability and performance compared to multiple OR conditions, but always check query plans.

Understanding how databases optimize IN with subqueries helps write efficient and correct SQL.