IN Operator

Learn how the IN operator lets you match a column against a list of possible values in a single, clean condition.

Introduction

In the last lesson you used BETWEEN to match a continuous range of values. But sometimes the values you care about are not a range at all — they are a specific, unrelated list, like grades 'A' or 'B', or product categories 'Books' and 'Toys'. That is exactly what the IN operator is built for.

This lesson covers how IN matches a column against a list of values, how it compares to chaining multiple OR conditions, and how NOT IN excludes a list instead.

What You Will Learn

What the IN operator does and how it matches against a list of values.
How IN is a cleaner shorthand for multiple OR conditions.
How to use NOT IN to exclude a list of values.
A preview of how IN can also be paired with a subquery.

What is IN?

The IN operator checks whether a column's value matches any value in a given list. If it matches at least one item in the list, the row is included in the results.

We will keep using our students table, with columns id, name, marks, and grade.

id	name	marks	grade
1	Alex	78	A
2	Sam	65	B
3	Priya	92	A
4	John	55	C
5	Meera	70	B
6	Arjun	45	D

Syntax

column_name IN (value1, value2, value3, ...)

Note

The list of values goes inside parentheses, separated by commas. Text values need quotes, like 'A', 'B', while numbers do not.

IN vs Multiple OR Conditions

Before IN, matching several possible values required chaining OR conditions together. This works, but it gets repetitive and harder to read as the list grows.

SELECT name, grade
FROM students
WHERE grade = 'A' OR grade = 'B';

IN expresses the exact same logic far more cleanly:

SELECT name, grade
FROM students
WHERE grade IN ('A', 'B');

Both queries return an identical result. As the list of values grows to five, ten, or more, IN becomes dramatically easier to read and maintain than a long chain of OR conditions.

Example: Grade A or B

Let us run the IN version and see the actual result.

grade_in.sql

SELECT name, grade
FROM students
WHERE grade IN ('A', 'B');

Result

+-------+-------+
| name  | grade |
+-------+-------+
| Alex  | A     |
| Sam   | B     |
| Priya | A     |
| Meera | B     |
+-------+-------+

John (grade C) and Arjun (grade D) are excluded because neither 'C' nor 'D' appears in the list 'A', 'B'.

NOT IN

Just like NOT BETWEEN, adding NOT before IN reverses the condition — it matches every row whose value is not found anywhere in the list.

SELECT name, grade
FROM students
WHERE grade NOT IN ('A', 'B');

Result

+-------+-------+
| name  | grade |
+-------+-------+
| John  | C     |
| Arjun | D     |
+-------+-------+

This returns only the students whose grade is neither 'A' nor 'B' — the exact opposite set from the previous example.

A Preview: IN with Subqueries

So far, every list you have passed to IN has been typed out by hand, like ('A', 'B'). But IN can also accept the result of another SELECT query, called a subquery, in place of that hand-typed list.

preview_only.sql

-- A sneak peek — subqueries get their own dedicated lesson later
SELECT name
FROM students
WHERE id IN (SELECT student_id FROM top_scorers);

Coming Later

You do not need to understand subqueries yet. For now, just remember that IN is not limited to a fixed, hand-typed list — it can also check membership against values produced dynamically by another query. This is covered in depth in the dedicated Subqueries lesson later in this course.

Common Mistakes

Avoid These Mistakes

Forgetting quotes around text values inside the list, like IN (A, B) instead of IN ('A', 'B').
Assuming IN uses OR-based logic differently from equality — it still only ever performs exact matches against each list item.
Confusing IN with BETWEEN — IN checks discrete values, not a continuous range.
Leaving the list empty, like IN (), which is valid syntax but will never match any row.

Best Practices

Reach for IN whenever you find yourself chaining three or more OR conditions on the same column.
Keep the list of values readable — one clear, comma-separated list rather than deeply nested logic.
Use NOT IN sparingly and carefully once NULL values are involved (this is covered in more depth in a later lesson).
Remember that IN can take a subquery once you reach that topic, avoiding the need to hand-type long lists.

Frequently Asked Questions

Is IN just shorthand for OR?

Yes. WHERE grade IN ('A', 'B') behaves identically to WHERE grade = 'A' OR grade = 'B'. IN is simply a cleaner way to write the same logic.

Can IN compare numbers as well as text?

Yes. IN works with any comparable data type, including numbers, strings, and dates, for example WHERE marks IN (65, 78, 92).

How many values can I put inside IN?

There is no meaningful limit for typical use. You can list as many values as you need, though extremely long lists are often better replaced with a subquery.

Does the order of values inside IN matter?

No. IN only checks for membership in the list, so the order you list the values in has no effect on the result.

Key Takeaways

IN checks whether a column's value matches any value in a given list.
IN is a cleaner, more readable shorthand for chaining multiple OR conditions.
NOT IN reverses the check, matching values that are absent from the list.
IN can also accept a subquery instead of a hand-typed list, a topic covered later in this course.

Summary

The IN operator lets you match a column against a list of acceptable values in one compact condition, replacing long chains of OR. NOT IN flips that logic to exclude the list instead.

In this lesson, you learned how IN and NOT IN work, saw them compared directly against equivalent OR conditions, and got a preview of how IN pairs with subqueries. Next, you will learn the LIKE operator, used for pattern matching on text.

Lesson 38 Completed

You understand how IN matches against a list of values.
You can rewrite chained OR conditions using IN.
You know how NOT IN excludes a list of values.
You have a preview of IN working with subqueries.

Next Lesson →