LIKE, BETWEEN and IN Operators

Choose the Right Operator

LIKE matches a text pattern, BETWEEN checks an inclusive range and IN tests membership in a list. They are readable alternatives to long combinations of comparison operators.

LIKE and Wildcards

SELECT full_name FROM students
WHERE full_name LIKE 'M%';

SELECT full_name FROM students
WHERE full_name LIKE '_a%';

% matches zero or more characters; _ matches exactly one. NOT LIKE reverses the pattern for known values. Case and accent sensitivity depend on collation, not on LIKE alone.

Literal Wildcards, Collation and Performance

If the searched text contains a real percent or underscore, escape it rather than letting it act as a wildcard. Define and test the escape behavior used by your client and SQL mode. A leading wildcard such as '%ana%' usually cannot use the left edge of a normal B-tree index efficiently.

BETWEEN: Inclusive Ranges

SELECT full_name, marks FROM students
WHERE marks BETWEEN 80 AND 90
ORDER BY marks;

It is equivalent to marks >= 80 AND marks <= 90 for ordinary values. For timestamps, prefer a half-open range so the next period boundary is unambiguous:

WHERE created_at >= '2026-08-01'
  AND created_at <  '2026-09-01'

IN Lists, NOT IN and NULL

SELECT full_name, class_name FROM students
WHERE class_name IN ('X-A', 'XI-A')
ORDER BY student_id;

IN is equivalent to equality against any listed non-NULL value. For a subquery, NOT IN needs special care: if the subquery returns NULL, intended non-matches may become UNKNOWN. Remove NULL explicitly or express the anti-match with a correctly correlated NOT EXISTS.

Mistakes and Practice

• Assuming LIKE is always case-sensitive.
• Forgetting that BETWEEN includes both ends.
• Using BETWEEN with reversed boundaries.
• Letting NOT IN receive NULL unexpectedly.
• Using a leading wildcard on a large frequently searched column without measuring.

1. Find names ending in a.
2. Return marks from 86.50 through 91.00 inclusive.
3. Use IN for X-A and X-B, then rewrite with OR.
4. Explain why a monthly timestamp report should use a half-open range.

Quick Summary

• LIKE matches patterns with % and _.
• BETWEEN includes both boundaries.
• IN tests a list cleanly; NOT IN plus NULL needs care.
• Collation, escaping, prepared values and indexing affect real behavior.

Official References

• Pattern Matching — MySQL 8.4
• Comparison Operators — MySQL 8.4
• Subqueries with ANY and IN

References reviewed 14 August 2026.