MySQL In and Between

Matching a List of Values with IN

Suppose you need every order placed from three specific regions: 'North', 'South', or 'West'. Written with OR, that's WHERE region = 'North' OR region = 'South' OR region = 'West' - readable for three values, tedious for ten. The IN operator collapses this into a single, tidy list: WHERE region IN ('North', 'South', 'West'). MySQL evaluates it exactly the same way, but the intent is clearer and the list is easier to maintain.

SELECT order_id, region, total_amount
FROM orders
WHERE region IN ('North', 'South', 'West');

Matching a Range with BETWEEN

BETWEEN checks whether a value falls within a range, inclusive of both endpoints. WHERE quantity BETWEEN 10 AND 20 is shorthand for WHERE quantity >= 10 AND quantity <= 20 - it matches 10, 20, and everything in between, not just the interior values. BETWEEN works on numbers, dates, and even strings, since MySQL compares them using their natural ordering.

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

Negating with NOT IN and NOT BETWEEN

Both operators can be reversed with NOT. NOT IN (...) matches rows whose value is absent from the list; NOT BETWEEN x AND y matches rows outside the range, on either side. Both keep the same inclusive behavior as their positive form - NOT BETWEEN 40 AND 50 excludes 40 and 50 themselves, since those values belong to the included range.

SELECT employee_id, first_name, age
FROM employees
WHERE age NOT BETWEEN 40 AND 50;

• IN accepts a fixed list of literals, or the result of a subquery: WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'Delhi').
• BETWEEN requires the lower bound first; WHERE price BETWEEN 100 AND 50 with the bounds reversed silently matches nothing - it does not swap them for you.
• Both operators can use an index efficiently: IN can look up each listed value, and BETWEEN can scan a contiguous range.
• For date columns that include a time component, BETWEEN '2025-01-01' AND '2025-01-31' can miss rows timestamped later in the day on the 31st - AND order_date < '2025-02-01' is safer for full-day coverage.