beginner 5 min read

Filtering with IN and BETWEEN

Written with a little help from AI, reviewed and edited by a human.

In the first post we learned how to filter data using WHERE with simple conditions like = or >. But sometimes you need to check against a list of values or a range of values. That’s where IN and BETWEEN come in.

The problem

Let’s say we have a table of students:

idnameagegrade
1Alice14A
2Bob16B
3Charlie15A
4Diana17C
5Eve13B
6Frank16A

Now imagine you want to find all students who got a grade of A or B. With what we know so far, you’d have to write:

SELECT * FROM students
WHERE grade = 'A' OR grade = 'B';

That works, but what if you had 10 different grades to check? It gets long and messy fast. This is where IN helps.

IN

IN lets you check if a value matches any value in a list. Instead of writing multiple OR conditions, you give it a list and it checks all of them for you.

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

This does the exact same thing as the OR example above, but it’s much cleaner. It’s saying: “give me all students where the grade is in this list: A or B.”

The result:

idnameagegrade
1Alice14A
2Bob16B
3Charlie15A
5Eve13B
6Frank16A

It works with numbers too:

SELECT * FROM students
WHERE id IN (1, 3, 5);

This returns Alice, Charlie, and Eve — the students with id 1, 3, and 5.

Real life example

Think of it like a bouncer at a party with a guest list. Instead of asking:

  • “Are you Alice? No? Are you Bob? No? Are you Charlie?”

The bouncer just checks one list: “Is your name in this list?” Much faster.

NOT IN

You can also flip it around with NOT IN to find everything that is not in the list:

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

This returns only Diana — the only student whose grade is not A or B.

idnameagegrade
4Diana17C

BETWEEN

BETWEEN lets you check if a value falls within a range. It’s great for numbers and dates.

Let’s say we want to find all students who are between 14 and 16 years old. Without BETWEEN we’d write:

SELECT * FROM students
WHERE age >= 14 AND age <= 16;

With BETWEEN this becomes:

SELECT * FROM students
WHERE age BETWEEN 14 AND 16;

Much cleaner. It’s saying: “give me all students where age is between 14 and 16, including 14 and 16.”

The result:

idnameagegrade
1Alice14A
2Bob16B
3Charlie15A
6Frank16A

Eve (age 13) and Diana (age 17) are left out because they fall outside the range.

BETWEEN includes both ends of the range. So BETWEEN 14 AND 16 includes 14, 15, and 16.

Real life example

Think of BETWEEN like a height requirement for a roller coaster. “You must be between 140cm and 190cm to ride.” If you’re exactly 140cm or exactly 190cm, you still get on. Anyone shorter or taller doesn’t.

NOT BETWEEN

Just like IN, you can flip it with NOT BETWEEN:

SELECT * FROM students
WHERE age NOT BETWEEN 14 AND 16;

This returns Eve (13) and Diana (17) — everyone outside that range.

When to use which

  • Use IN when you have a specific list of values to check against. Example: specific grades, specific cities, specific user IDs.
  • Use BETWEEN when you have a range with a start and end. Example: ages, prices, dates.

Both are just cleaner ways to write WHERE conditions that you could technically write with OR and AND, but they make your SQL much easier to read.

Share: