Module 9 Set Matching with IN and NOT IN

Module 9

Set Matching with IN and NOT IN

A step-by-step, hands-on SQL practice lesson — with a live SQL playground

SELECT student_name, room FROM class_room WHERE room IN ('A101', 'B201');

After learning how to filter rows with WHERE, I can make my queries much cleaner when I need to match a column against several possible values. That is where IN becomes useful. Instead of writing many OR conditions, I can place the allowed values inside one list. In this lesson, I practice both IN and NOT IN with the class_room table, learn the important NULL behavior, and build queries step by step until the pattern feels natural.

Contents

1. Introduction
2. Requirements
3. Practice: Step by Step
4. Common Problems & Solutions
5. Conclusion
6. Homework & Quiz
7. Live SQL Playground
8. Related Lessons
9. FAQ

Introduction

What You'll Learn — and Why IN Matters

I use IN when a column can match one of several specific values. For example, if I want students in rooms A101, B201, or C301, I do not need to write three separate equality checks. I can use one readable condition:

WHERE room IN ('A101', 'B201', 'C301')

NOT IN reverses the membership test. It lets me find rows whose value is not in the listed set.

🎯 Objective

By the end of this lesson, you will be able to:

• Explain what a set of matching values means in SQL.
• Use IN to match a column against several exact values.
• Use NOT IN to exclude several exact values.
• Convert long OR conditions into cleaner IN conditions.
• Use IN with numbers, text values, and other SQL expressions.
• Combine IN or NOT IN with AND, OR, and other filters.
• Understand why NULL can make NOT IN behave unexpectedly.
• Choose between IN, NOT IN, BETWEEN, and comparison operators.

⚠️ What to Avoid

Important set-matching warnings

• Do not confuse IN with a range. IN (8, 9, 10) means those exact values, not every value between 8 and 10.
• Do not forget quotes around string values such as 'active' or 'A101'.
• Be careful with NOT IN when NULL values are possible. SQL's three-valued logic can produce no match for rows involving NULL.
• Do not put duplicate values in a list just because they are allowed. Duplicates do not add a new match.
• Do not build an enormous hard-coded list when a lookup table or subquery would be easier to maintain.

💡 Important to Know

The core idea

column IN (value1, value2, value3) is a compact way of asking whether the column equals any one of the listed values. Conceptually, room IN ('A101','B201') is similar to room = 'A101' OR room = 'B201'.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, Linux, or another operating system that can run MySQL. The SQL concepts in this lesson are portable across these systems.

Database ServerMySQL installed and running. If you need installation help, see How to Install MySQL Step by Step for Beginners.

Optional: XAMPPXAMPP plus phpMyAdmin provides a visual way to run the SQL. See How to Install XAMPP Step by Step for Beginners (Complete Guide 2026).

Prior knowledgeYou should know basic SELECT and WHERE. For a refresher, review Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide).

No installation is required for the browser playground below. The real practice steps are written for MySQL or phpMyAdmin.

Practice

Step-by-Step Practice: IN and NOT IN

I recommend following these steps in order. I first prepare the database, then I test exact matching, replace repeated OR conditions with IN, and finally practice NOT IN, NULL handling, and subqueries.

1 Open MySQL or phpMyAdmin

On Windows, macOS, or Linux, open your MySQL client. If you use XAMPP, start MySQL and open phpMyAdmin. In the terminal, I can connect with:

mysql -u root -p

The basic IN syntax is:

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

The opposite syntax is:

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

2 Create a Database and Verify It

I create a separate practice database so the examples are easy to repeat without changing another project.

CREATE DATABASE module9_in_practice;
SHOW DATABASES;
USE module9_in_practice;

After SHOW DATABASES;, confirm that module9_in_practice appears in the database list.

3 Create the class_room Table

Now I create a small table with repeated categories such as rooms and statuses. These repeated values are ideal for practicing set matching.

CREATE TABLE class_room (
  id INT PRIMARY KEY,
  student_name VARCHAR(80),
  grade_level INT,
  score DECIMAL(5,2),
  status VARCHAR(20),
  room VARCHAR(20)
);

Verify the database table and its columns:

SHOW TABLES;
DESCRIBE class_room;

4 Insert Sample Data

I insert eight students so the same room and status values appear more than once.

INSERT INTO class_room (id, student_name, grade_level, score, status, room) VALUES
(1, 'Dara', 7, 62.50, 'active', 'A101'),
(2, 'Sokha', 8, 74.00, 'inactive', 'A101'),
(3, 'Maly', 8, 81.50, 'active', 'B201'),
(4, 'Vannak', 9, 88.00, 'pending', 'B201'),
(5, 'Lina', 9, 93.50, 'active', 'C301'),
(6, 'Rith', 10, 69.00, 'inactive', 'C301'),
(7, 'Nita', 10, 97.00, 'active', 'D401'),
(8, 'Bora', 11, 55.00, 'pending', 'D401');

SELECT * FROM class_room ORDER BY id;

5 Match Several Text Values with IN

Suppose I want students in rooms A101, B201, and C301. I can write one clean condition:

SELECT student_name, room
FROM class_room
WHERE room IN ('A101', 'B201', 'C301')
ORDER BY room, student_name;

This is equivalent to writing:

SELECT student_name, room
FROM class_room
WHERE room = 'A101'
   OR room = 'B201'
   OR room = 'C301'
ORDER BY room, student_name;

My practice note

When I see several equality checks against the same column, I look for an opportunity to use IN. It usually makes the intention easier to read.

6 Match Several Numeric Values with IN

IN also works with numbers. If I want students in grades 7, 9, or 11, I can write:

SELECT student_name, grade_level
FROM class_room
WHERE grade_level IN (7, 9, 11)
ORDER BY grade_level, student_name;

Notice the difference between a set and a range:

-- Exact set: only 7, 9, or 11
WHERE grade_level IN (7, 9, 11)

-- Continuous range: every value from 7 through 11
WHERE grade_level BETWEEN 7 AND 11;

This distinction is important: IN is about membership in a specific list, while BETWEEN is about a continuous range.

7 Use NOT IN to Exclude a Set

Now I want every student except those in rooms A101 and D401.

SELECT student_name, room
FROM class_room
WHERE room NOT IN ('A101', 'D401')
ORDER BY room, student_name;

Conceptually, this is similar to:

WHERE room <> 'A101'
  AND room <> 'D401'

So NOT IN is especially useful when the list of excluded values grows.

8 Combine IN or NOT IN with Other Conditions

Real SQL queries often combine set matching with another filter. For example, I can find active students in selected rooms:

SELECT student_name, room, status
FROM class_room
WHERE status = 'active'
  AND room IN ('A101', 'B201', 'C301')
ORDER BY room, student_name;

I can also combine a numeric set with a score condition:

SELECT student_name, grade_level, score
FROM class_room
WHERE grade_level IN (8, 9, 10)
  AND score >= 75
ORDER BY grade_level, score DESC;

For more complex logic, parentheses make my intention clearer:

SELECT student_name, room, status
FROM class_room
WHERE (room IN ('A101', 'B201') OR status = 'pending')
  AND grade_level >= 8;

9 Understand NULL with NOT IN

This is one of the most important details I want to remember. SQL does not treat NULL as an ordinary value. A condition involving NULL can evaluate to UNKNOWN, not simply true or false.

For example, if a column can contain NULL, I should not assume that this always returns every row outside the listed values:

WHERE room NOT IN ('A101', 'B201')

If I specifically want rows whose room is either outside the list or NULL, I can say that explicitly:

WHERE room NOT IN ('A101', 'B201')
   OR room IS NULL

For larger projects, NOT EXISTS can also be a safer and clearer option when the exclusion list comes from another table or subquery, especially when NULLs may be present.

Troubleshooting

5 Common Problems and Solutions

Problem 1: I forgot quotes around text values

I wrote WHERE room IN (A101, B201) and MySQL reports an error or interprets the names incorrectly.

SolutionString values should normally be written with quotes: WHERE room IN ('A101', 'B201').

Problem 2: I expected IN to include every number in a range

I wrote grade_level IN (7, 11) and expected grades 7 through 11.

SolutionIN matches only the exact listed values. Use BETWEEN 7 AND 11 for a continuous inclusive range.

Problem 3: NOT IN behaves strangely when NULL exists

I expected NOT IN to return every row that is not one of the listed values, but rows involving NULL do not appear.

SolutionRemember SQL's NULL logic. If NULL should be included, add OR column_name IS NULL. When excluding values from another query, consider NOT EXISTS.

Problem 4: My IN list contains duplicate values

I have IN ('A101', 'A101', 'B201') and wonder whether the duplicate changes the result.

SolutionDuplicates do not create duplicate result rows. Keep the list unique because it is clearer and easier to maintain.

Problem 5: My IN condition is mixed with AND and OR incorrectly

The query returns more rows than I expected after I add several logical conditions.

SolutionUse parentheses to make the intended groups explicit. Test each condition separately, then combine them one at a time.

Conclusion

What I Want You to Remember

The main lesson I take from Module 9 is simple: IN is for matching a value against a specific set, while NOT IN is for excluding that set.

In my own SQL practice, I find IN especially helpful when I see repeated conditions such as room = 'A101' OR room = 'B201' OR room = 'C301'. Replacing them with room IN ('A101', 'B201', 'C301') makes the query easier to scan.

Expected result from this lesson

You should now be able to use IN and NOT IN for exact set matching, combine them with other filters, distinguish them from BETWEEN, and recognize the special care required when NULL values are involved.

Next step: keep practicing SQL filtering by combining the operators you have learned into complete real-world queries.

Homework

Homework & Quiz

📝 Homework Task

Using the class_room table, write these queries yourself before checking the live playground:

1. Find students whose room is one of A101, B201, or C301.
2. Find students whose room is not A101 or D401.
3. Find students whose grade level is exactly 7, 9, or 11.
4. Find active students whose room is in A101, B201, or C301 and whose score is at least 75.
5. Create a query that demonstrates how you would include NULL rooms while excluding A101 and B201.

🧪 Quiz: Test Your Understanding (10 Questions)

Pick one answer per question, then click Submit to see your score instantly.

1. What does room IN ('A101', 'B201') mean?

The room must be between A101 and B201 The room must equal A101 or B201 The room must contain both values The room is automatically sorted

2. Which syntax correctly matches several status values?

WHERE status MATCH ('active', 'pending') WHERE status = ('active', 'pending') WHERE status IN ('active', 'pending') WHERE status LIST 'active', 'pending'

3. What does NOT IN ('A101', 'D401') do?

Excludes rows whose value is A101 or D401 Returns only A101 and D401 Finds every value between A101 and D401 Sorts rooms alphabetically

4. What is the main difference between IN and BETWEEN?

They are always identical BETWEEN only works with text IN creates a table IN matches listed exact values, while BETWEEN describes a continuous range

5. Which condition is equivalent to grade_level IN (7, 9, 11)?

grade_level BETWEEN 7 AND 11 grade_level = 7 OR grade_level = 9 OR grade_level = 11 grade_level > 7 AND grade_level < 11 grade_level <> 7 AND grade_level <> 9

6. What should you remember about NULL and NOT IN?

NULL always matches NOT IN NULL is treated as zero NULL uses SQL's unknown logic, so it may need explicit IS NULL handling NULL is automatically converted to an empty string

7. Which query finds active students in rooms A101 or B201?

WHERE status = 'active' AND room IN ('A101', 'B201') WHERE status IN active AND room = A101, B201 WHERE active BETWEEN room A101 AND B201 WHERE status = active OR room IN A101, B201

8. Do duplicate values inside an IN list create duplicate result rows?

Yes, always Only on Windows Only with numbers No, but duplicates make the query less tidy

9. What is a good reason to use IN instead of many OR comparisons?

It changes the database structure It automatically creates an index It makes membership in a specific set concise and readable It removes all NULL values

10. What is the main idea of Module 9?

IN sorts rows IN matches exact values from a set, while NOT IN excludes the listed set IN only works with dates NOT IN creates a new database

0 / 10

Try It Live

🧪 Live SQL Playground

This browser playground uses SQLite through WebAssembly. It is designed to practice the set-matching ideas in this lesson; use your real MySQL server for MySQL-specific testing.

class_roomid INTEGER PK
student_name TEXT
grade_level INTEGER
score REAL
status TEXT
room TEXT

— Load an example query — List all students IN selected rooms NOT IN selected rooms IN exact grades 7, 9, 11 IN active or pending Active + IN rooms IN grades + score IN + OR + AND

SELECT * FROM class_room ORDER BY id;

Loading SQL engine…

Run a query to see results here.

Related Lessons

Continue Learning SQL — Modules 1 to 8

Module 1: Mastering the SQL SELECT Statement

Module 2: Selecting Specific Columns and Using Aliases

Module 3: DISTINCT — Deduplicating Results

Module 4: Filtering with the WHERE Clause

Module 5: Numeric Comparisons

Module 6: SQL Combining Conditions

Module 7: Logical Inversion with NOT

Module 8: Range Filtering with BETWEEN and NOT BETWEEN

FAQ

Frequently Asked Questions

1. What does IN do in SQL?

IN checks whether a column value matches one of the exact values in a specified list.

2. What does NOT IN do?

NOT IN checks for values that are not members of the listed set. NULL requires special consideration.

3. Is IN the same as BETWEEN?

No. IN matches a discrete list of exact values. BETWEEN describes a continuous inclusive range.

4. Can IN be used with numbers?

Yes. For example, grade_level IN (7, 9, 11) matches exactly 7, 9, or 11.

5. Can IN be used with text?

Yes. String values should normally be quoted, such as status IN ('active', 'pending').

6. Is IN better than many OR conditions?

When the same column is compared against several exact values, IN is usually more concise and easier to read. The optimizer can still choose an appropriate execution plan.

7. Why should I be careful with NOT IN and NULL?

SQL uses three-valued logic. Comparisons involving NULL can evaluate to UNKNOWN, so NULL rows may not be returned by NOT IN. Use explicit IS NULL handling when needed.

8. Can I combine IN with AND and OR?

Yes. You can combine set matching with other conditions. Use parentheses when several AND/OR conditions could be interpreted in different ways.

9. Can IN use a subquery?

Yes. A common pattern is WHERE department_id IN (SELECT id FROM departments ...). For exclusions with possible NULLs, NOT EXISTS may be a safer alternative.

10. Does IN remove duplicate rows?

No. IN only controls which rows qualify. If the underlying query produces duplicate rows, use an appropriate technique such as DISTINCT when deduplication is actually required.

Part of the CodeMend SQL Module series. Keep practicing exact set matching, exclusions, logical combinations, and NULL handling.