IN, BETWEEN and LIKE in SQL

IN tests membership in a list of values. It is the readable equivalent of x = 1 OR x = 2 OR x = 3, and the optimizer usually treats the two identically — the generated plan is the same either way. The reasons to prefer IN are readability, and the fact that you can also use a subquery on the right-hand side.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  status VARCHAR(20) NOT NULL,
  assigned_to INTEGER
);

INSERT INTO tickets (id, title, status, assigned_to) VALUES
  (1, 'Login page times out', 'open',    11),
  (2, 'Invoice PDF is blank', 'open',    12),
  (3, 'Export times out',    'open',    NULL),
  (4, 'Dark mode request',   'closed',  11),
  (5, 'Slow search results', 'pending', NULL);

SELECT id, title, status
FROM tickets
WHERE status IN ('open', 'pending')
ORDER BY id;

Three rows come back: 1, 2 and 3 with status open, and 5 with status pending. Ticket 4 is closed and is excluded.

Three practical differences from an OR chain. First, IN keeps the value list visually together, so a reviewer can check it at a glance instead of parsing four separate clauses. Second, IN accepts a subquery — WHERE assigned_to IN (SELECT id FROM engineers WHERE team = 'platform') — which keeps the rule in one place instead of hard-coding a list of ids. Third, and this is the one people miss, an empty IN list is a syntax error in most engines, while an empty OR chain is simply not written at all. If the list comes from application code and might be empty, guard it before building the query.

One NULL detail. WHERE x IN (1, 2, NULL) returns only rows where x is 1 or 2 — the NULL in the list cannot match anything, because the comparison with NULL is UNKNOWN. That means an IN list accidentally containing NULL behaves like a filter rather than like the universal match some people expect.

BETWEEN is inclusive on both ends. WHERE score BETWEEN 60 AND 80 matches 60, 80 and everything in between. It is exactly equivalent to WHERE score >= 60 AND score <= 80, and you can write it that way when you want the boundary to be open.

Two BETWEEN edge cases come up in interviews. With a date column, WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31' excludes anything recorded during the afternoon of the 31st, because the upper bound is midnight at the start of that day. Write < '2026-04-01' instead. And BETWEEN is only meaningful on ordered types — numeric ranges, dates, alphabetic ranges for a prefix search — never on an unordered value.

LIKE is the pattern-matching operator. The two wildcards are % for any run of characters and _ for exactly one character. WHERE title LIKE 'Export%' matches titles starting with Export; WHERE title LIKE '%timeout%' matches titles containing timeout anywhere; WHERE title LIKE 'Export _' matches Export followed by one character.

The alternatives depend on what you are really asking. If you want an exact set of values, use IN rather than a chain of LIKE comparisons with the = operator. If you want a prefix search, remember that LIKE 'Export%' can use an index in most engines while LIKE '%timeout%' cannot — it has no left anchor, so the engine must scan. If you need case-insensitive matching, note that SQLite's LIKE is already ASCII case-insensitive, while MySQL's depends on the collation and PostgreSQL needs ILIKE or lower(col) LIKE lower(?). And if you are doing real full-text search — words, relevance, stemming — LIKE is the wrong tool entirely; use a full-text index or a dedicated search engine.