IN & BETWEEN — MySQL

IN, NOT IN, and BETWEEN in MySQL

The IN and BETWEEN operators let you filter rows against a set of values or a range without writing long chains of OR conditions. They are among the most readable — and most misunderstood — tools in SQL. This page covers every behavioral nuance, including the notorious NULL trap inside NOT IN.

The IN Operator

IN tests whether a column value matches any value in a list. It is syntactic sugar over multiple OR equality checks and produces the same execution plan for short lists.

SQL

-- Without IN (verbose, error-prone when modifying)
SELECT * FROM orders
WHERE status = 'pending'
   OR status = 'processing'
   OR status = 'shipped';

-- With IN (clean, easy to add or remove values)
SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- Works with numbers too
SELECT product_id, name, price
FROM products
WHERE category_id IN (3, 7, 12, 15, 20);

-- Works with dates
SELECT order_id, created_at
FROM orders
WHERE DATE(created_at) IN ('2024-01-01', '2024-07-04', '2024-12-25');

Note

Both the verbose OR version and the IN version produce identical results and typically identical execution plans for short lists. IN is preferred for readability and maintainability.

NOT IN

NOT IN excludes rows whose column value appears in the list. It is the logical complement of IN.

SQL

-- Orders that are not in a terminal state
SELECT order_id, customer_id, total
FROM orders
WHERE status NOT IN ('delivered', 'cancelled', 'refunded')
ORDER BY created_at DESC;

-- Products not in selected categories
SELECT product_id, name, price, category_id
FROM products
WHERE category_id NOT IN (1, 5, 9)
  AND is_active = 1;

Warning

If the IN list contains even one NULL value, NOT IN returns zero rows for the entire query. NULL comparisons use three-valued logic — NULL is never equal to (or not equal to) anything, so the entire expression becomes UNKNOWN. This is the most common IN/NOT IN bug in production SQL.

NULL Behavior Inside IN Lists — The Full Story

Let's trace exactly why a NULL in the list breaks NOT IN. MySQL expands col NOT IN (1, 2, NULL) into: col <> 1 AND col <> 2 AND col <> NULL

The last comparison col <> NULL is always UNKNOWN (not TRUE or FALSE). AND-ing anything with UNKNOWN produces UNKNOWN, not TRUE. Since WHERE only keeps rows where the condition is TRUE, no rows pass.

SQL

-- Demonstration: NOT IN with NULL in the list returns no rows
CREATE TEMPORARY TABLE demo (id INT);
INSERT INTO demo VALUES (10), (20), (30);

SELECT id FROM demo WHERE id NOT IN (5, 10, NULL);
-- Returns: (empty result) — zero rows!

-- Fix 1: ensure the list never contains NULL
SELECT id FROM demo WHERE id NOT IN (5, 10);
-- Returns: 20, 30

-- Fix 2: filter NULLs before applying NOT IN on a column that might be NULL
SELECT * FROM products
WHERE category_id IS NOT NULL
  AND category_id NOT IN (1, 2);

-- Fix 3: use NOT EXISTS (completely NULL-safe)
SELECT p.*
FROM products AS p
WHERE NOT EXISTS (
  SELECT 1
  FROM (SELECT 1 AS id UNION ALL SELECT 2) AS excluded
  WHERE excluded.id = p.category_id
);

IN with a Subquery

The list inside IN can be replaced by a subquery that returns a single column. This is one of the most powerful and common SQL patterns.

SQL

-- Customers who placed at least one order in 2024
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE YEAR(created_at) = 2024
);

-- Products that appear in at least one active (non-cancelled) order
SELECT product_id, name, price
FROM products
WHERE product_id IN (
  SELECT DISTINCT oi.product_id
  FROM order_items AS oi
  JOIN orders      AS o ON oi.order_id = o.order_id
  WHERE o.status NOT IN ('cancelled', 'refunded')
);

-- Employees in departments that have a budget over $500,000
SELECT employee_id, full_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE annual_budget > 500000
);

Tip

When the subquery can return many rows, EXISTS is often faster because MySQL stops after finding the first match. Use IN (subquery) for short result sets and when readability matters more than micro-optimization.

BETWEEN x AND y

BETWEEN tests whether a value falls within an inclusive range. Both endpoints are always included. It is shorthand for col >= x AND col <= y.

SQL

-- Numeric range (both endpoints included)
SELECT product_id, name, price
FROM products
WHERE price BETWEEN 10.00 AND 50.00;

-- Equivalent explicit form
SELECT product_id, name, price
FROM products
WHERE price >= 10.00 AND price <= 50.00;

-- Integer range
SELECT employee_id, full_name, age
FROM employees
WHERE age BETWEEN 25 AND 40;

Note

The lower bound must come first. BETWEEN 50 AND 10 returns zero rows because no value can satisfy value >= 50 AND value <= 10 simultaneously. MySQL does not automatically swap the bounds.

NOT BETWEEN

SQL

-- Products outside the mid-range price band
SELECT product_id, name, price
FROM products
WHERE price NOT BETWEEN 10.00 AND 50.00
ORDER BY price;
-- Returns: price < 10.00 OR price > 50.00

-- Employees NOT in the standard age band
SELECT employee_id, full_name, age, department
FROM employees
WHERE age NOT BETWEEN 30 AND 55
ORDER BY age;

BETWEEN with DATE Columns

BETWEEN works seamlessly with DATE columns. Both endpoints are fully included.

SQL

-- DATE column: both dates fully included
SELECT order_id, customer_id, total, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- This is equivalent to:
-- order_date >= '2024-01-01' AND order_date <= '2024-03-31'

BETWEEN with DATETIME Columns — The Midnight Trap

With DATETIME and TIMESTAMP columns, the upper-bound date stops at midnight (00:00:00) on that day. Events at 09:30 on March 31 are excluded because 2024-03-31 09:30:00 is greater than 2024-03-31 (which MySQL treats as 2024-03-31 00:00:00).

SQL

-- Naive approach misses events after midnight on the last day
SELECT order_id, created_at
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31';
-- Misses all orders on March 31 after 00:00:00 !

-- Fix 1: add the time component explicitly
WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-03-31 23:59:59';

-- Fix 2: best practice — use >= and < next boundary (handles microseconds too)
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-04-01';

Tip

The >= start AND < next_period pattern is the safest way to filter DATETIME ranges. It handles microsecond precision (e.g., 2024-03-31 23:59:59.999999) and is easier to generate programmatically.

BETWEEN with Strings

SQL

-- Alphabetical range using the column's collation
SELECT last_name, first_name, department
FROM employees
WHERE last_name BETWEEN 'A' AND 'Nzzz'
ORDER BY last_name;

-- Note: 'N' alone misses names like 'Nathan', so pad with z's for the upper bound
-- or use: last_name >= 'A' AND last_name < 'O'

Performance: IN vs OR for Large Lists

For small lists (under ~20 values), IN and chained OR produce identical execution plans. For larger lists, IN is preferred because:

The optimizer can sort the list and use binary search during evaluation.
Index range scans work with IN lists via the multi-range read optimization.
The optimizer can use index merges across multiple range conditions.

SQL

-- Inspect execution plan for IN
EXPLAIN SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
-- Look for: type=range, key=idx_status — index scan, not full table scan

-- For hundreds of IDs, temp table + JOIN outperforms IN
CREATE TEMPORARY TABLE target_ids (id INT PRIMARY KEY);
INSERT INTO target_ids VALUES (101),(205),(318) /* ... thousands more */;

SELECT o.*
FROM orders AS o
JOIN target_ids AS t ON o.customer_id = t.id;

DROP TEMPORARY TABLE target_ids;

Combining IN and BETWEEN in One Query

SQL

-- E-commerce: orders eligible for a loyalty discount
-- Q4 2023, total $100–$500, delivered, no existing discount code
SELECT
  o.order_id,
  c.email,
  o.total,
  o.created_at
FROM orders    AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.created_at >= '2023-10-01'
  AND o.created_at <  '2024-01-01'
  AND o.total BETWEEN 100.00 AND 500.00
  AND o.status IN ('delivered', 'shipped')
  AND o.discount_code IS NULL
ORDER BY o.total DESC;

-- HR: software engineers hired in a two-year window in active departments
SELECT
  employee_id,
  full_name,
  hire_date,
  job_code
FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2023-12-31'
  AND job_code IN ('SWE1', 'SWE2', 'SWE3', 'PM1')
  AND department_id NOT IN (
    SELECT department_id
    FROM departments
    WHERE is_archived = 1
  );

Checking for NULL with BETWEEN

SQL

-- BETWEEN returns NULL (not FALSE) when the column is NULL
-- So NULL rows are excluded from both BETWEEN and NOT BETWEEN results

SELECT COUNT(*) FROM products WHERE price BETWEEN 10 AND 50;
-- Products with NULL price are NOT counted

-- To include NULL prices:
SELECT COUNT(*) FROM products
WHERE price BETWEEN 10 AND 50 OR price IS NULL;

Practical Real-World Example — Inventory Analysis

SQL

-- Find products in active categories that need restocking
-- (stock between 1 and 10, in specific categories, not discontinued)
SELECT
  p.product_id,
  p.name,
  cat.name            AS category,
  p.stock_quantity,
  p.reorder_level,
  p.price
FROM products    AS p
JOIN categories  AS cat ON p.category_id = cat.category_id
WHERE p.category_id IN (3, 7, 12, 15)
  AND p.stock_quantity BETWEEN 1 AND 10
  AND p.stock_quantity <= p.reorder_level
  AND p.status NOT IN ('discontinued', 'archived')
  AND cat.is_active = 1
ORDER BY p.stock_quantity ASC, cat.name;

Quick Reference Summary

Operator	Meaning	NULL-safe?	Performance note
IN (list)	Value equals any item in the list	Partial — NULLs in list affect NOT IN	Good — optimizer can sort list
NOT IN (list)	Value matches none of the items	No — NULL in list returns zero rows	Use NOT EXISTS if NULLs possible
IN (subquery)	Value matches any row in subquery	No — NULL rows affect NOT IN	EXISTS is faster for large subqueries
BETWEEN x AND y	x <= value <= y (both inclusive)	Yes — NULL col gives NULL result	Uses index range scan
NOT BETWEEN x AND y	value < x OR value > y	Yes — NULL col gives NULL result	Uses index range scan

Default to IN for membership tests against a short, known list
Use BETWEEN for numeric or date range filters where both bounds are inclusive
Use >= and < (not BETWEEN) for DATETIME upper bounds to avoid the midnight trap
When the subquery behind NOT IN may return NULLs, switch to NOT EXISTS
For large dynamic ID lists (hundreds+), load them into a temp table and JOIN