Matching Multiple Values Using IN and NOT IN in MySQL – Detailed Notes

Course: MySQL Training

Topic: Matching Multiple Values Using IN and NOT IN

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

---

1. Introduction to IN and NOT IN in MySQL

MySQL provides several operators for filtering records from a database table. The IN and NOT IN operators are especially useful when a query needs to compare a column against multiple possible values.

The IN operator selects records when a column matches any value in a specified list or the result of a subquery. The NOT IN operator excludes records whose column values match the specified list or subquery results.

For example, a company may want to retrieve employees working in IT, HR, or Finance without writing multiple OR conditions. Similarly, an online store may want to display products that are not included in a list of discontinued or restricted products.

Both operators make SQL queries easier to read, maintain, and understand.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the purpose of the IN operator in MySQL.
• Understand how NOT IN excludes specified values.
• Write queries that match multiple text and numeric values.
• Replace repeated OR conditions with IN.
• Use IN and NOT IN with SELECT statements.
• Combine IN and NOT IN with AND, OR, and other filtering conditions.
• Use IN and NOT IN with subqueries.
• Understand how NULL values affect IN and NOT IN.
• Identify common mistakes when matching multiple values.
• Apply these operators to employee, customer, product, order, and student databases.

3. What Is the IN Operator?

The IN operator checks whether a value matches any value in a specified list. If a match is found, the condition evaluates to TRUE.

It is commonly used inside the WHERE clause to filter records based on multiple acceptable values.

3.1 Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, value3);

Explanation:

• SELECT specifies the columns to retrieve.
• FROM specifies the table containing the data.
• WHERE defines the filtering condition.
• IN checks whether the column value matches any value in the list.
• The values inside parentheses form the list of accepted values.

3.2 Example Using IN

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Finance');

This query retrieves employees whose department is IT, HR, or Finance. Employees from other departments are excluded.

4. Creating a Sample Database

To understand IN and NOT IN through practical examples, we will create a sample company database containing employee information.

4.1 Creating the Database

CREATE DATABASE company_db;
USE company_db;

4.2 Creating the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    city VARCHAR(50),
    salary DECIMAL(10,2),
    experience INT,
    status VARCHAR(20)
);

4.3 Inserting Sample Records

INSERT INTO employees
(employee_id, employee_name, department, city, salary, experience, status)
VALUES
(1, 'Amit Sharma', 'IT', 'Mumbai', 65000, 5, 'Active'),
(2, 'Priya Verma', 'HR', 'Pune', 48000, 3, 'Active'),
(3, 'Rahul Mehta', 'IT', 'Delhi', 75000, 7, 'Active'),
(4, 'Sneha Patil', 'Finance', 'Mumbai', 55000, 4, 'Inactive'),
(5, 'Vikas Singh', 'Sales', 'Pune', 42000, 2, 'Active'),
(6, 'Neha Joshi', 'IT', 'Mumbai', 52000, 3, 'Active'),
(7, 'Karan Shah', 'HR', 'Delhi', 60000, 6, 'Inactive'),
(8, 'Pooja Desai', 'Finance', 'Pune', 70000, 8, 'Active'),
(9, 'Rohan Kulkarni', 'Sales', 'Mumbai', 45000, 2, 'Inactive'),
(10, 'Ananya Rao', 'IT', 'Pune', 80000, 9, 'Active');

4.4 Displaying All Records

SELECT * FROM employees;

This query displays all employee records. We will use this table to demonstrate how IN and NOT IN work with different data types and filtering conditions.

5. Using IN with Text Values

The IN operator can match multiple text values stored in columns such as department, city, status, category, or product name.

5.1 Matching Multiple Departments

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Finance');

This query returns employees who belong to any of the three specified departments.

The query is equivalent to the following condition:

SELECT employee_name, department
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
   OR department = 'Finance';

Using IN makes the query shorter and easier to maintain, particularly when the list contains many values.

5.2 Matching Multiple Cities

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Pune');

This query retrieves employees located in Mumbai or Pune. Employees from Delhi are not included.

5.3 Matching Multiple Status Values

SELECT employee_name, status
FROM employees
WHERE status IN ('Active', 'Inactive');

This query returns employees whose status is either Active or Inactive. If these are the only non-NULL status values in the table, all such employees will be returned.

5.4 Matching Several Departments and Displaying More Columns

SELECT employee_id, employee_name, department, city, salary
FROM employees
WHERE department IN ('IT', 'Finance');

The query returns employee details for IT and Finance departments, including their locations and salaries.

6. Using IN with Numeric Values

The IN operator can also compare numeric columns against a list of numbers. It is useful for searching selected IDs, order numbers, product IDs, or numeric categories.

6.1 Matching Specific Employee IDs

SELECT employee_id, employee_name, department
FROM employees
WHERE employee_id IN (1, 3, 6, 10);

This query returns only the employees whose IDs are 1, 3, 6, or 10.

6.2 Matching Specific Salary Values

SELECT employee_name, salary
FROM employees
WHERE salary IN (48000, 55000, 65000, 80000);

This query selects employees whose salary exactly matches one of the listed amounts. It does not select all employees whose salaries fall between the smallest and largest values.

6.3 Matching Specific Experience Values

SELECT employee_name, experience
FROM employees
WHERE experience IN (2, 5, 8);

This query retrieves employees with exactly 2, 5, or 8 years of experience.

6.4 Matching Multiple Numeric IDs in a Report

SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id IN (2, 4, 7, 9)
ORDER BY employee_id;

The query selects the specified employee records and sorts the results by employee ID.

7. What Is the NOT IN Operator?

The NOT IN operator excludes records whose column values match any value in a specified list. It is useful when a query must return all values except certain values.

For example, a company may want to display employees who do not belong to the HR or Sales departments.

7.1 Basic Syntax

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

The condition is true when the column value is not equal to any listed value, subject to SQL NULL handling.

7.2 Example: Excluding Multiple Departments

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('HR', 'Sales');

This query returns employees whose department is neither HR nor Sales. Employees with a NULL department are not returned by this condition.

7.3 Example: Excluding Multiple Cities

SELECT employee_name, city
FROM employees
WHERE city NOT IN ('Mumbai', 'Delhi');

This query excludes employees based in Mumbai and Delhi. Employees from other non-NULL cities, such as Pune, may be returned.

7.4 Example: Excluding Specific Employee IDs

SELECT employee_id, employee_name
FROM employees
WHERE employee_id NOT IN (1, 3, 5, 7);

This query returns employees whose IDs are not 1, 3, 5, or 7.

7.5 Example: Excluding Specific Salary Values

SELECT employee_name, salary
FROM employees
WHERE salary NOT IN (42000, 45000, 48000);

This query excludes employees earning exactly ₹42,000, ₹45,000, or ₹48,000. It does not exclude every salary below ₹48,000; only the specified exact values are excluded.

8. Difference Between IN and NOT IN

Feature	IN	NOT IN
Purpose	Matches any value in the specified list.	Excludes values matching the specified list.
Filtering behavior	Includes matching records.	Includes records that do not match any listed value, subject to NULL rules.
Example	city IN ('Mumbai', 'Pune')	city NOT IN ('Mumbai', 'Pune')
Typical use	Find employees from selected cities.	Find employees outside selected cities.
NULL behavior	NULL column values generally do not match a list of ordinary non-NULL values.	NULL column values do not satisfy the condition; a NULL in the list can also cause unexpected filtering results.

9. Combining IN with AND

IN can be combined with AND when a query must match one of several values and also satisfy another condition.

9.1 Example: IT or Finance Employees Earning More Than ₹55,000

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
  AND salary > 55000;

This query returns employees who work in IT or Finance and earn more than ₹55,000.

9.2 Example: Employees from Mumbai or Pune with Active Status

SELECT employee_name, city, status
FROM employees
WHERE city IN ('Mumbai', 'Pune')
  AND status = 'Active';

This query returns active employees who are based in Mumbai or Pune.

9.3 Example: Selected Departments with Minimum Experience

SELECT employee_name, department, experience
FROM employees
WHERE department IN ('IT', 'HR', 'Finance')
  AND experience >= 4;

This query selects employees from the specified departments who have at least four years of experience.

9.4 Example: Selected Employee IDs with Active Status

SELECT employee_id, employee_name, status
FROM employees
WHERE employee_id IN (1, 2, 3, 6, 10)
  AND status = 'Active';

Only employees who meet both requirements are included: their ID must be listed, and their status must be Active.

10. Combining NOT IN with AND

NOT IN is frequently used with AND to exclude certain values while applying additional filtering requirements.

10.1 Example: Excluding HR and Sales from Active Employees

SELECT employee_name, department, status
FROM employees
WHERE department NOT IN ('HR', 'Sales')
  AND status = 'Active';

This query returns active employees whose department is neither HR nor Sales.

10.2 Example: Excluding Cities and Setting a Salary Threshold

SELECT employee_name, city, salary
FROM employees
WHERE city NOT IN ('Delhi')
  AND salary >= 50000;

This query retrieves employees who are not based in Delhi and earn at least ₹50,000.

10.3 Example: Excluding Several Departments and Requiring Experience

SELECT employee_name, department, experience
FROM employees
WHERE department NOT IN ('HR', 'Sales')
  AND experience >= 4;

This query excludes HR and Sales employees and returns employees in other departments with at least four years of experience.

11. Combining IN with OR

OR can combine separate filtering requirements. IN can simplify one part of a condition while OR handles another alternative.

11.1 Example: Selected Departments or a High Salary

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
   OR salary >= 75000;

This query returns employees who work in IT or Finance, or whose salary is at least ₹75,000. Employees who meet both conditions are also included.

11.2 Example: Selected Cities or a Specific Department

SELECT employee_name, city, department
FROM employees
WHERE city IN ('Mumbai', 'Pune')
   OR department = 'HR';

The query returns employees who are located in Mumbai or Pune, as well as employees in HR regardless of their city.

11.3 Example: Grouping Conditions with Parentheses

SELECT employee_name, department, city, status
FROM employees
WHERE status = 'Active'
  AND (department IN ('IT', 'Finance')
       OR city = 'Pune');

The query returns active employees who either work in IT or Finance or are located in Pune. Parentheses make the intended grouping clear.

12. Combining NOT IN with OR

NOT IN can also be combined with OR when multiple alternative conditions are involved.

12.1 Example: Employees Outside Selected Departments or from Mumbai

SELECT employee_name, department, city
FROM employees
WHERE department NOT IN ('HR', 'Sales')
   OR city = 'Mumbai';

This query returns employees who are outside HR and Sales or who are located in Mumbai. An employee may qualify through either condition.

12.2 Example: Excluding Selected Cities or Selecting Finance Employees

SELECT employee_name, city, department
FROM employees
WHERE city NOT IN ('Delhi')
   OR department = 'Finance';

This query includes employees who are not located in Delhi and also includes Finance employees, even if they are based in Delhi.

When OR is combined with other conditions, always verify whether the logic matches the business requirement.

13. Using IN with BETWEEN and Comparison Operators

IN can be combined with numeric comparisons and range conditions to build more precise queries.

13.1 Example: Selected Departments with a Salary Range

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
  AND salary BETWEEN 50000 AND 80000;

This query selects employees in IT or Finance whose salaries are between ₹50,000 and ₹80,000, including both boundaries.

13.2 Example: Selected Departments with Salary Above a Threshold

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'HR')
  AND salary >= 60000;

This query returns IT or HR employees earning at least ₹60,000.

13.3 Example: Selected Experience Values and Active Status

SELECT employee_name, experience, status
FROM employees
WHERE experience IN (3, 5, 7, 9)
  AND status = 'Active';

This query matches employees whose experience is exactly 3, 5, 7, or 9 years and whose status is Active.

14. Using IN with ORDER BY and LIMIT

IN can be used alongside ORDER BY to sort results and LIMIT to restrict the number of rows returned.

14.1 Example: Selected Departments Sorted by Salary

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
ORDER BY salary DESC;

The query returns employees from IT and Finance, ordered from the highest salary to the lowest.

14.2 Example: Displaying the Top Three Matching Employees

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
ORDER BY salary DESC
LIMIT 3;

This query returns up to three employees from the selected departments with the highest salaries.

14.3 Example: Excluding Departments and Limiting Results

SELECT employee_name, department, salary
FROM employees
WHERE department NOT IN ('HR', 'Sales')
ORDER BY salary DESC
LIMIT 5;

This query displays up to five highest-paid employees outside HR and Sales.

15. Using IN with Subqueries

A subquery is a query nested inside another SQL statement. IN can use a subquery to compare a column against values returned by another query.

This is useful when the list of matching values is stored in another table and changes over time.

15.1 Example: Selecting Employees from Departments in Another Table

First, create a departments table:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    is_open TINYINT(1)
);

Insert sample records:

INSERT INTO departments
(department_id, department_name, is_open)
VALUES
(1, 'IT', 1),
(2, 'HR', 1),
(3, 'Finance', 1),
(4, 'Sales', 0);

Now retrieve employees who belong to departments that are currently open:

SELECT employee_name, department
FROM employees
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE is_open = 1
);

The subquery returns department names where is_open = 1. The outer query selects employees whose department matches one of those names.

15.2 Example: Employees in Departments with a High Average Salary

SELECT employee_name, department, salary
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING AVG(salary) > 60000
);

The subquery identifies departments whose average salary exceeds ₹60,000. The outer query returns employees working in those departments.

When the source column can contain NULL values, consider how those values affect the subquery and the IN comparison.

16. Using NOT IN with Subqueries

NOT IN can exclude records based on values returned by another query. This is useful for finding customers without selected categories, employees outside an approved group, or products not listed in another table.

16.1 Example: Employees Outside Open Departments

SELECT employee_name, department
FROM employees
WHERE department NOT IN (
    SELECT department_name
    FROM departments
    WHERE is_open = 1
      AND department_name IS NOT NULL
);

This query selects employees whose non-NULL department is not among the departments marked as open. The NULL check in the subquery helps avoid the common NOT IN issue caused by NULL values in its result set.

16.2 Example: Customers Without Orders

Assume the database has a customers table and an orders table. The following query finds customers whose IDs do not appear in the orders table, provided the subquery returns no NULL customer IDs.

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

This query can work correctly when the subquery excludes NULL values. However, NOT EXISTS is often a clearer alternative for this type of exclusion.

16.3 Alternative Using NOT EXISTS

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS checks whether the subquery returns a matching row. It avoids the specific problem of a NULL in the subquery list causing NOT IN to evaluate to UNKNOWN for otherwise unmatched values.

17. Understanding NULL Values with IN and NOT IN

NULL represents a missing or unknown value in MySQL. It is not equivalent to zero, an empty string, or the text 'NULL'. NULL handling is especially important when using NOT IN.

17.1 IN with a NULL Column Value

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'HR');

If an employee's department is NULL, that row does not match either listed value and is not returned.

17.2 NOT IN with a NULL Column Value

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('IT', 'HR');

Rows with NULL department values are not returned because the condition evaluates to UNKNOWN rather than TRUE.

17.3 The Problem with NULL Inside a NOT IN List

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('IT', NULL);

This query can return no rows for ordinary non-NULL department values because the comparison against the NULL list item makes the NOT IN result UNKNOWN when no matching value is found. A WHERE clause only returns rows for which the condition evaluates to TRUE.

17.4 Safer Approach When Excluding Values

SELECT employee_name, department
FROM employees
WHERE department IS NOT NULL
  AND department NOT IN ('IT', 'HR');

This query explicitly excludes missing department values and excludes IT and HR. Whether NULL rows should be excluded or included depends on the business requirement.

17.5 Including NULL Values in the Results

If the requirement is to return employees whose department is not IT or HR, including employees with no department assigned, write the condition explicitly:

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('IT', 'HR')
   OR department IS NULL;

The OR condition includes records with a missing department as well as records outside the two listed departments.

18. IN and NOT IN with Different Data Types

IN and NOT IN can be used with text, numeric, date, and other comparable values. The values should be compatible with the data type of the column.

18.1 Text Columns

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'Finance');

This matches department names against a list of text values.

18.2 Numeric Columns

SELECT employee_name, salary
FROM employees
WHERE salary IN (42000, 55000, 80000);

This matches exact numeric salary values.

18.3 Date Columns

Assume an orders table contains an order_date column of type DATE.

SELECT order_id, order_date
FROM orders
WHERE order_date IN ('2026-01-10', '2026-02-15', '2026-03-20');

This query returns orders placed on any of the specified dates. Date strings should use a format appropriate for the DATE column, such as YYYY-MM-DD.

18.4 Excluding Specific Dates

SELECT order_id, order_date
FROM orders
WHERE order_date NOT IN ('2026-01-10', '2026-02-15');

This query excludes orders placed on the two listed dates. Rows with a NULL order date will not match this condition.

19. IN Versus Multiple OR Conditions

IN and repeated OR conditions can often express the same requirement when comparing one column against several values.

19.1 Using OR

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
   OR city = 'Pune'
   OR city = 'Delhi';

19.2 Using IN

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Pune', 'Delhi');

Both queries match employees whose city is one of the three listed values. The IN version is generally easier to read and update.

Feature	IN	Multiple OR Conditions
Readability	Compact when checking many values in one column.	Can become lengthy when many alternatives are present.
Maintenance	Easy to add or remove list values.	Requires editing individual comparisons.
Use case	One column compared against a list.	Alternative conditions that may involve different columns or expressions.
Performance	Depends on the query plan, indexes, and data distribution.	May be optimized similarly for equivalent conditions.

IN is not automatically faster in every situation. For equivalent conditions, MySQL's optimizer may produce similar execution plans.

20. Common Mistakes and Their Solutions

20.1 Forgetting Parentheses

Incorrect:

SELECT employee_name
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
  AND salary > 55000;

This query evaluates AND before OR. All IT employees can match regardless of salary, while the salary condition applies to HR employees.

Correct when the salary requirement applies to both departments:

SELECT employee_name
FROM employees
WHERE department IN ('IT', 'HR')
  AND salary > 55000;

20.2 Using IN for a Range

Incorrect if the goal is to retrieve all salaries from ₹50,000 to ₹70,000:

SELECT employee_name, salary
FROM employees
WHERE salary IN (50000, 60000, 70000);

This query matches only the exact listed values.

Correct for an inclusive range:

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

20.3 Using NOT IN with a NULL in a Subquery

Potentially problematic:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

If the subquery returns NULL, the NOT IN comparison can produce UNKNOWN for otherwise unmatched customer IDs.

Improved version:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

Another option is to use NOT EXISTS.

20.4 Forgetting Quotes Around Text Values

Incorrect:

SELECT employee_name
FROM employees
WHERE department IN (IT, HR);

Correct:

SELECT employee_name
FROM employees
WHERE department IN ('IT', 'HR');

Text values should normally be enclosed in single quotes.

20.5 Using the Wrong Column

Ensure that the column in the WHERE clause contains the values being checked. For example, compare department names with the department column rather than the employee name column.

20.6 Expecting NOT IN to Include NULL Values

NOT IN does not automatically include rows with NULL in the tested column. Use an explicit OR condition with IS NULL when those records should be included.

20.7 Listing Too Many Values Manually

If the list of values is large or changes frequently, consider storing those values in a separate table and using a subquery or join. This makes the query easier to maintain.

21. Best Practices for IN and NOT IN

• Use IN when a column must match any value from a known list.
• Use NOT IN when values from a known list must be excluded.
• Prefer IN over repeated OR comparisons when checking one column against several values.
• Use BETWEEN instead of IN when the requirement is a continuous numeric or date range.
• Check for NULL values when using NOT IN with subqueries.
• Use IS NULL and IS NOT NULL for explicit missing-value checks.
• Use NOT EXISTS as an alternative for many exclusion queries involving subqueries.
• Keep values compatible with the data type of the column.
• Use parentheses when combining IN or NOT IN with AND and OR.
• Use clear indentation for long queries.
• Test queries against sample data before applying them to production tables.
• Use indexes where appropriate and examine the query plan when performance matters.
• Use parameterized queries in application code rather than building SQL strings from untrusted user input.
• For a frequently changing list of values, consider storing the list in a table instead of hardcoding it into a query.

22. Performance Considerations

The performance of IN and NOT IN depends on the size of the table, the number of values in the list, available indexes, data distribution, and the execution plan selected by MySQL.

22.1 Indexing Columns Used for Filtering

If department filtering is common in the application, an index on the department column may be useful.

CREATE INDEX idx_employees_department
ON employees (department);

For frequent city-based filtering, an index on city may also help:

CREATE INDEX idx_employees_city
ON employees (city);

Indexes require storage and add overhead to INSERT, UPDATE, and DELETE operations. Create them based on actual query patterns and performance testing.

22.2 Using EXPLAIN

The EXPLAIN statement shows how MySQL plans to execute a query.

EXPLAIN
SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
  AND salary > 50000;

Review the execution plan to understand how tables are accessed and whether available indexes may be used.

22.3 Large Lists of Values

Very large hardcoded lists can be difficult to maintain and may make queries cumbersome. A reference table or temporary table may be more appropriate when the list is large, reused, or frequently updated.

23. Practical Business Examples

23.1 Employee Management

Requirement: Find active employees in IT, Finance, or HR.

SELECT employee_id, employee_name, department, status
FROM employees
WHERE department IN ('IT', 'Finance', 'HR')
  AND status = 'Active';

23.2 Customer Management

Requirement: Find customers from selected cities.

SELECT customer_id, customer_name, city
FROM customers
WHERE city IN ('Mumbai', 'Pune', 'Delhi');

23.3 Product Management

Requirement: Exclude discontinued or recalled products.

SELECT product_id, product_name, status
FROM products
WHERE status NOT IN ('Discontinued', 'Recalled');

Rows with a NULL status are not included. If missing status values should be included, add an appropriate IS NULL condition.

23.4 Order Management

Requirement: Find orders with selected statuses.

SELECT order_id, customer_id, order_status
FROM orders
WHERE order_status IN ('Pending', 'Processing', 'Shipped');

23.5 Student Management

Requirement: Find students enrolled in selected courses.

SELECT student_id, student_name, course_name
FROM students
WHERE course_name IN ('MySQL', 'Python', 'Web Development');

23.6 Sales Analysis

Requirement: Exclude cancelled and returned orders.

SELECT order_id, region, amount, status
FROM orders
WHERE status NOT IN ('Cancelled', 'Returned')
  AND amount > 5000;

This query selects orders above ₹5,000 whose status is neither Cancelled nor Returned. If status is NULL, those rows are excluded unless the query is adjusted to include them.

24. Step-by-Step Method for Writing IN and NOT IN Queries

1. Identify the table: Determine which table contains the records to retrieve.
2. Identify the column: Choose the column whose value must match or exclude a list.
3. Prepare the values: List the acceptable or excluded values.
4. Choose the operator: Use IN for matching and NOT IN for exclusion.
5. Add other conditions: Combine the list condition with AND or OR when necessary.
6. Review NULL behavior: Check whether the column or subquery can contain NULL values.
7. Execute the SELECT query: Verify the returned records.
8. Validate the results: Confirm that the query matches the intended requirement.
9. Check performance: Use EXPLAIN when appropriate, especially for large datasets.

25. Practice Exercises

Use the sample employees table to complete the following exercises.

1. Display employees working in IT, HR, and Finance.
2. Display employees located in Mumbai or Pune.
3. Display employees whose IDs are 1, 4, 6, or 9.
4. Display employees who do not work in HR or Sales.
5. Display employees whose salary exactly matches ₹42,000, ₹55,000, or ₹80,000.
6. Display employees with 2, 4, 5, or 8 years of experience.
7. Display active employees who belong to IT or Finance.
8. Display employees outside Mumbai and Delhi who earn at least ₹45,000.
9. Display employees in IT, HR, or Finance whose experience is at least three years.
10. Display employees in selected departments, sorted by salary in descending order.
11. Use IN with a subquery to retrieve employees who belong to open departments.
12. Write a query that includes employees with NULL department values while excluding IT and HR.

26. Practice Exercise Solutions

Exercise 1: Employees in IT, HR, and Finance

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Finance');

Exercise 2: Employees from Mumbai or Pune

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Pune');

Exercise 3: Specific Employee IDs

SELECT employee_id, employee_name
FROM employees
WHERE employee_id IN (1, 4, 6, 9);

Exercise 4: Employees Outside HR and Sales

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('HR', 'Sales');

Exercise 5: Specific Salary Values

SELECT employee_name, salary
FROM employees
WHERE salary IN (42000, 55000, 80000);

Exercise 6: Selected Experience Values

SELECT employee_name, experience
FROM employees
WHERE experience IN (2, 4, 5, 8);

Exercise 7: Active IT or Finance Employees

SELECT employee_name, department, status
FROM employees
WHERE department IN ('IT', 'Finance')
  AND status = 'Active';

Exercise 8: Employees Outside Mumbai and Delhi Earning at Least ₹45,000

SELECT employee_name, city, salary
FROM employees
WHERE city NOT IN ('Mumbai', 'Delhi')
  AND salary >= 45000;

Exercise 9: Selected Departments with Minimum Experience

SELECT employee_name, department, experience
FROM employees
WHERE department IN ('IT', 'HR', 'Finance')
  AND experience >= 3;

Exercise 10: Selected Departments Sorted by Salary

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
ORDER BY salary DESC;

Exercise 11: Employees in Open Departments

SELECT employee_name, department
FROM employees
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE is_open = 1
);

Exercise 12: Include NULL Departments While Excluding IT and HR

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('IT', 'HR')
   OR department IS NULL;

27. Mini Project: Employee and Department Filtering

Project Objective

Create a set of MySQL queries that use IN and NOT IN to generate employee reports based on department, city, salary, experience, and employment status.

Project Requirements

• Retrieve employees from a selected list of departments.
• Find employees from a list of cities.
• Exclude employees from specific departments.
• Retrieve employees with selected salary or experience values.
• Combine IN with AND to filter active employees earning above a threshold.
• Use NOT IN to exclude unwanted statuses.
• Use a subquery to identify employees belonging to open departments.
• Test how NULL values affect NOT IN results.
• Sort the results and limit the number of displayed records.

Sample Project Query

SELECT
    employee_id,
    employee_name,
    department,
    city,
    salary,
    experience,
    status
FROM employees
WHERE department IN ('IT', 'Finance', 'HR')
  AND city IN ('Mumbai', 'Pune')
  AND salary >= 50000
  AND status NOT IN ('Inactive')
ORDER BY salary DESC;

This query retrieves employees who work in IT, Finance, or HR; are located in Mumbai or Pune; earn at least ₹50,000; and do not have an Inactive status. NULL status values are excluded by NOT IN.

28. Interview Questions and Answers

Q1. What is the IN operator in MySQL?

IN checks whether a value matches any value in a specified list or the result of a subquery.

Q2. What is the NOT IN operator?

NOT IN excludes values that match a specified list or subquery result, subject to SQL NULL behavior.

Q3. What is the main difference between IN and NOT IN?

IN includes matching values, whereas NOT IN excludes matching values.

Q4. Can IN replace multiple OR conditions?

Yes. When checking one column against several possible values, IN often provides a shorter and clearer alternative to repeated OR conditions.

Q5. Can IN be used with numeric values?

Yes. It can match employee IDs, salaries, product IDs, experience values, and other compatible numeric values.

Q6. Can IN be used with text values?

Yes. It can match values such as department names, cities, statuses, and categories.

Q7. Can IN be used with a subquery?

Yes. A subquery can return a set of values against which the outer query compares a column.

Q8. What happens when the column value is NULL in an IN comparison?

The comparison generally evaluates to UNKNOWN unless the condition explicitly checks for NULL using IS NULL.

Q9. Why can NOT IN produce unexpected results when a subquery returns NULL?

A NULL value can make the NOT IN comparison evaluate to UNKNOWN for values that do not match any listed non-NULL value. Filter NULLs from the subquery or consider NOT EXISTS.

Q10. How do you include NULL values when using NOT IN?

Add an explicit condition such as OR column_name IS NULL if missing values should be included.

Q11. What is the difference between IN and BETWEEN?

IN matches a list of exact values, while BETWEEN checks whether a value falls within an inclusive range.

Q12. Can IN be combined with AND?

Yes. IN can match one of several values while AND enforces additional requirements.

Q13. Can IN be combined with OR?

Yes. It can be combined with OR to match either a list-based condition or another condition.

Q14. Does NOT IN automatically include NULL values?

No. Rows where the tested column is NULL do not satisfy NOT IN when the list contains ordinary non-NULL values.

Q15. Is IN always faster than OR?

No. Performance depends on the execution plan, indexes, table size, and data distribution. MySQL may optimize equivalent conditions similarly.

Q16. When should a separate table be used instead of a long IN list?

A separate table may be preferable when the list is large, reused, or frequently changed. A subquery or join can then retrieve the required values dynamically.

Q17. What is NOT EXISTS used for?

NOT EXISTS checks whether a subquery has no matching row. It is often useful for exclusion queries and avoids the particular NULL-list issue associated with NOT IN.

Q18. How can you inspect the execution plan for an IN query?

Use EXPLAIN before the SELECT statement to inspect how MySQL plans to access the data.

29. Quick Revision Notes

• IN matches any value in a list.
• NOT IN excludes values in a list.
• IN can simplify multiple OR conditions on the same column.
• IN works with text, numeric, date, and other comparable values.
• IN can use a subquery as its value source.
• NOT IN can be combined with AND, OR, and other conditions.
• IN matches exact values; it does not define a continuous range.
• Use BETWEEN for inclusive ranges.
• NULL values can affect IN and especially NOT IN comparisons.
• Use IS NULL and IS NOT NULL for missing-value checks.
• Consider NOT EXISTS for exclusion queries involving subqueries.
• Use parentheses when combining list-based conditions with AND and OR.
• Use EXPLAIN and suitable indexes when investigating performance.

30. Final Checklist

• Can you explain how IN matches multiple values?
• Can you use NOT IN to exclude several values?
• Can you replace repeated OR conditions with IN?
• Can you combine IN with AND and OR?
• Can you use IN and NOT IN with numeric and text columns?
• Can you use a subquery with IN?
• Can you explain why NULL can cause problems with NOT IN?
• Can you write a query that includes NULL values explicitly?
• Can you decide when BETWEEN or NOT EXISTS is more appropriate?
• Can you validate your results and investigate query performance?

31. Conclusion

The IN and NOT IN operators are essential MySQL tools for matching multiple values and excluding unwanted values. IN is particularly useful when a column needs to match any item in a list, while NOT IN is useful when certain values must be excluded.

These operators can be combined with AND, OR, comparison operators, ORDER BY, LIMIT, and subqueries to build practical database queries. Understanding NULL behavior is especially important when using NOT IN with subqueries because a NULL value can prevent expected records from being returned.

With regular practice, learners can use IN and NOT IN to create cleaner, more maintainable queries for employee management, customer analysis, product filtering, order reporting, and other database applications.

To learn more about MySQL and practical database skills, visit the MySQL Training Course. You can also register for a course demo to explore the training program.