IN, BETWEEN, and LIKE

Three operators that replace long chains of OR with something a human can read.

IN — one of a list

SQL▶ Run
SELECT first_name, dept_id
FROM employees
WHERE dept_id IN (1, 2)
ORDER BY dept_id, first_name;

Identical to dept_id = 1 OR dept_id = 2, and it stays readable at ten values where the OR chain does not. It works on text too:

SQL▶ Run
SELECT order_id, status, amount
FROM orders
WHERE status IN ('pending', 'cancelled')
ORDER BY order_id;

The NOT IN trap

NOT IN with a NULL anywhere in the list returns nothing at all:

SQL▶ Run
SELECT COUNT(*) AS surprising_zero
FROM employees
WHERE dept_id NOT IN (1, 2, NULL);

Zero rows. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and that last comparison is never true, so the whole AND chain can never be true. This bites hardest when the list comes from a subquery that happens to contain a NULL — the query returns nothing, no error is raised, and the report simply shows a blank page. NOT EXISTS is the safe form, and gets its own lesson later.

BETWEEN — an inclusive range

SQL▶ Run
SELECT first_name, hired_on
FROM employees
WHERE hired_on BETWEEN '2020-01-01' AND '2021-12-31'
ORDER BY hired_on;

BETWEEN a AND b means >= a AND <= b. Both ends are included — the most common misreading, and the reason off-by-one bugs cluster here.

Two rules worth internalising:

• The low value goes first. BETWEEN 100 AND 1 matches nothing, silently.
• Do not use BETWEEN on timestamps. BETWEEN '2024-03-01' AND '2024-03-31' misses everything that happened during the last day, because a timestamp on the 31st at 09:00 is greater than the 31st at midnight. Use a half-open range instead — >= '2024-03-01' AND < '2024-04-01' — which is correct regardless of month length, leap years, or how many decimal places the timestamps carry.

Half-open on our date-only column, for comparison:

SQL▶ Run
SELECT order_id, placed_at, amount
FROM orders
WHERE placed_at >= '2024-03-01' AND placed_at < '2024-04-01'
ORDER BY placed_at;

LIKE — pattern matching on text

Two wildcards:

SQL▶ Run
SELECT first_name, email
FROM employees
WHERE email LIKE 'a%'
ORDER BY email;

More patterns:

SQL▶ Run
SELECT name, category
FROM products
WHERE name LIKE '%a%'
ORDER BY name;

• 'a%' — starts with a
• '%plan' — ends with plan
• '%wid%' — contains wid
• '_at' — three characters ending in "at"

Case sensitivity, again

LIKE follows the same rules as =: case-sensitive on PostgreSQL, Oracle and SQLite (though SQLite's LIKE is case-insensitive for ASCII by default — a genuine inconsistency with its own =), usually insensitive on MySQL and SQL Server. PostgreSQL offers ILIKE for an explicitly case-insensitive match; the portable version is LOWER(col) LIKE LOWER('pattern').

Matching a literal %

To search for a percent sign, escape it: LIKE '100!%' ESCAPE '!'. Any character can be the escape character; \ is conventional but must still be declared with ESCAPE.

The performance note

LIKE 'abc%' can use an index — the database knows where to start looking. LIKE '%abc' and LIKE '%abc%' cannot: with an unknown prefix, every row must be examined. On a large table that is the difference between a millisecond and a minute, and it is why search boxes that do leading-wildcard matching are the first thing to fall over under load. Full-text search exists for this.

Common mistakes

• NOT IN over a list containing NULL — returns nothing, silently.
• Assuming BETWEEN is exclusive — it includes both ends.
• BETWEEN on timestamps — loses the final day.
• LIKE '%x%' on a large table — no index can help.
• LIKE with no wildcard — LIKE 'Ada' is just = 'Ada', written slower.

Interview question

Why can NOT IN (SELECT …) return zero rows when you expect many?

If the subquery yields even one NULL, every comparison in the expanded AND chain becomes unknown and no row qualifies. Use NOT EXISTS, or filter the NULLs out of the subquery.

Check yourself

1. Rewrite dept_id = 1 OR dept_id = 3 OR dept_id = 5 with IN.
2. Write a March 2024 filter that is correct even if placed_at gains a time.
3. Which of LIKE 'ab%', LIKE '%ab', LIKE '%ab%' can use an index?