Set Matching with IN and NOT IN
A step-by-step, hands-on SQL practice lesson — with a live SQL playground
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.
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
INto match a column against several exact values. - Use
NOT INto exclude several exact values. - Convert long
ORconditions into cleanerINconditions. - Use
INwith numbers, text values, and other SQL expressions. - Combine
INorNOT INwithAND,OR, and other filters. - Understand why
NULLcan makeNOT INbehave unexpectedly. - Choose between
IN,NOT IN,BETWEEN, and comparison operators.
⚠️ What to Avoid
- Do not confuse
INwith 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 INwhen 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
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
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;
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 NULLFor 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.
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.
IN 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.
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.
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.
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.
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:
- Find students whose room is one of A101, B201, or C301.
- Find students whose room is not A101 or D401.
- Find students whose grade level is exactly 7, 9, or 11.
- Find active students whose room is in A101, B201, or C301 and whose score is at least 75.
- 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?
2. Which syntax correctly matches several status values?
3. What does NOT IN ('A101', 'D401') do?
4. What is the main difference between IN and BETWEEN?
5. Which condition is equivalent to grade_level IN (7, 9, 11)?
6. What should you remember about NULL and NOT IN?
7. Which query finds active students in rooms A101 or B201?
8. Do duplicate values inside an IN list create duplicate result rows?
9. What is a good reason to use IN instead of many OR comparisons?
10. What is the main idea of Module 9?
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.
student_name TEXT
grade_level INTEGER
score REAL
status TEXT
room TEXT
Related Lessons
Continue Learning SQL — Modules 1 to 8
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.
