MySQL IN and BETWEEN – Powerful Filtering Techniques
When you need to filter data based on multiple values or a range of values, MySQL provides two incredibly handy operators: IN and BETWEEN.
In this tutorial, we’ll explore how to use IN and BETWEEN in practical scenarios using employees and departments tables.
📌 What is MySQL IN?
The IN operator allows you to check if a value matches any value in a list of values.
✅ Syntax
🧪 Examples Using IN
1️⃣ Find Employees in Department IDs 2, 4, and 7
🟢 Result: Returns all employees who belong to departments 2, 4, or 7.
2️⃣ Find Employees with Designations ‘Manager’, ‘Analyst’, ‘HR’
🟢 Result: Returns employees with specific job roles.
3️⃣ Find Departments Named ‘Sales’, ‘IT’, or ‘Marketing’
🟢 Result: Returns only those departments that match the list exactly.
4️⃣ Combine with NOT
🟢 Result: Excludes employees from departments 3 and 5.
📌 What is MySQL BETWEEN?
The BETWEEN operator is used to filter results within a specific range. It works for numbers, dates, and even text (alphabetical order).
✅ Syntax
📝 Note: BETWEEN is inclusive, meaning it includes both value1 and value2.
🧪 Examples Using BETWEEN
1️⃣ Find Employees with Salaries Between 30,000 and 50,000
🟢 Result: Returns employees whose salaries fall within that range.
2️⃣ Find Employees Who Joined Between Two Dates
🟢 Result: Filters employees who joined during the year 2023.
3️⃣ Find Departments with IDs Between 1 and 5
🟢 Result: Returns department records with IDs from 1 to 5.
✅ Combine IN and BETWEEN in One Query
🟢 Result: Returns employees from selected departments whose salary falls in the specified range.
🧠 Summary Table
| Operator | Use Case | Example |
|---|---|---|
IN |
Match any value in a list | WHERE dept_id IN (2, 4, 5) |
BETWEEN |
Match value within a range | WHERE salary BETWEEN 30k AND 5 |
🚀 Real-World Tips (From Acesoftech Academy)
-
Prefer
INover multipleORconditions – it’s cleaner and easier to maintain. -
When using
BETWEENwith dates, always use ISO format (YYYY-MM-DD). -
Combine both to write powerful, concise filters for dashboards and reports.
🏁 Conclusion
Both IN and BETWEEN are must-have tools in your MySQL toolbox. Whether you’re selecting multiple specific values or filtering based on a range, these operators help make your queries shorter and more readable.
Try these queries on your own employees and departments tables — and build your SQL skills one query at a time!
