IN, BETWEEN and LIKE: shorter conditions

IN checks a value against a list, BETWEEN checks a range (both ends included), and LIKE matches text against a pattern with % (any number of characters) and _ (exactly one).

Why it matters

status = 'shipped' OR status = 'pending' OR status = 'cancelled' is long and easy to get wrong. Questions like "names starting with P" or "orders in February" come up all the time. These three words make such conditions short and readable.

The idea

IN: status IN ('shipped', 'pending') is true when status equals any value in the list.
BETWEEN: amount BETWEEN 50 AND 100 means amount >= 50 AND amount <= 100, ends included.
LIKE: product_name LIKE 'P%' matches text starting with P; % stands for any number of characters (even none) and _ for exactly one.
Put NOT in front of any of them to flip it: NOT IN, NOT BETWEEN, NOT LIKE.

IN is a guest list: is your name on it? BETWEEN is a height limit at a fair ride: between 1.2 m and 1.9 m, both ends allowed. LIKE is a crossword clue with blanks: "P _ _" fits Pen.

A guest list doesn't care about missing names, but SQL does: if the list after NOT IN contains a NULL, nothing matches at all. Unit 4 shows this trap.

Syntax

WHERE column IN (value1, value2)
WHERE column BETWEEN low AND high
WHERE text_column LIKE 'pattern%'

How it works

  1. status IN ('shipped', 'pending') is checked against each order: shipped matches, pending matches, delivered doesn't.
  2. order_date BETWEEN '2024-02-01' AND '2024-02-29' keeps every date from the 1st to the 29th of February, both days included.
  3. product_name LIKE 'P%' keeps Pen and Phone case: P, then anything.
  4. LIKE '%s' keeps names ending in s: Headphones.
  5. LIKE '_en' means exactly one character, then "en": Pen matches, but Phone case doesn't.

Key points

Common mistakes

Not true: LIKE 'P' matches every value that starts with P.
In fact: Without a wildcard, LIKE matches only the exact text P. Write LIKE 'P%' for the values that start with P.

In the interview

IN tests membership in a list, and is shorthand for several ORs. BETWEEN tests an inclusive range. LIKE matches text patterns, where percent is any sequence of characters and underscore is exactly one. Two cautions: for timestamps I use a half-open range — greater-or-equal the start and less-than the next day — rather than BETWEEN, and a leading-wildcard LIKE can't use a B-tree index.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.