IN, BETWEEN & LIKE — Use IN, BETWEEN, LIKE, and pattern matching - SQL

SECTION 1

Historical Context & Motivation

The need to filter rows from relational tables was evident from the earliest days of database research. Edgar F. Codd's seminal 1970 paper introduced the relational model, describing selection operations that restrict tuples based on predicate logic. As IBM researchers translated Codd's algebra into an executable language — first SEQUEL, then SQL — they recognized that raw Boolean conjunctions of equality tests were verbose and error-prone for common patterns such as membership tests, range checks, and substring searches. The IN, BETWEEN, and LIKE operators emerged as syntactic conveniences that made SQL more readable while mapping cleanly onto the underlying relational algebra's selection operator (σ).

1970 • Codd's Relational Model

Edgar F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," defining selection (σ) as the fundamental row-filtering operation on relations.

1974 • SEQUEL at IBM

Chamberlin and Boyce develop SEQUEL (Structured English Query Language), introducing the WHERE clause with comparison predicates that would later include IN, BETWEEN, and LIKE.

1986 • SQL-86 (ANSI Standard)

The first ANSI/ISO SQL standard formally specifies the IN predicate for set membership, the BETWEEN predicate for inclusive range testing, and the LIKE predicate with '%' and '_' wildcards.

1999 • SQL:1999 — SIMILAR TO

SQL:1999 introduces the SIMILAR TO predicate, blending LIKE syntax with full regular expression character classes, broadening pattern matching capabilities within the standard.

2010s • Vendor Extensions

Major RDBMS vendors (PostgreSQL, MySQL, SQL Server) add proprietary regex operators (REGEXP, ~, RLIKE) while retaining the classic IN, BETWEEN, and LIKE as the portable foundation.

Without these operators, a query that checks whether a column's value belongs to a set of five items would require five separate equality comparisons joined by OR. A range filter would need two comparisons with AND. Pattern searches would be entirely impossible within standard SQL syntax. The central question these operators answer is: How can we express common filtering patterns concisely, readably, and in a way the query optimizer can exploit?

SECTION 2

Core Principles & Definitions

All three operators live inside the WHERE clause (or HAVING clause) and evaluate to a Boolean predicate for each row. They are shorthand for combinations of basic comparison and logical operators, and the query optimizer is free to rewrite them into equivalent forms during query planning. Understanding each operator's semantics, boundary behavior, and NULL handling is essential for writing correct, performant queries.

IN — Set Membership

Tests whether a value matches any element in a specified list or subquery result set. Equivalent to chaining multiple OR-ed equality checks. Supports NOT IN for exclusion.

BETWEEN — Inclusive Range

Checks whether a value falls within an inclusive lower and upper bound. Equivalent to value >= low AND value <= high. Works with numbers, dates, and strings.

LIKE — Pattern Matching

Matches string values against a pattern using two wildcards: '%' (zero or more characters) and '_' (exactly one character). Case sensitivity depends on the collation.

ESCAPE — Literal Wildcards

The ESCAPE clause lets you search for literal '%' or '_' characters by designating an escape character. For example, LIKE '%10\%%' ESCAPE '\' matches strings containing '10%'.

NULL Awareness

All three operators follow SQL's three-valued logic. Any comparison with NULL yields UNKNOWN, not TRUE or FALSE. NOT IN with a NULL in the list can return no rows — a common pitfall.

✦

KEY TAKEAWAY

Think of IN as a guest list at a venue door — the bouncer checks your name against the list and lets you through if there is a match. BETWEEN is like a speed-limit zone — any speed reading within the lower and upper signs is acceptable. LIKE is a search engine with simple wildcards — you type a partial query and the engine fills in the blanks. Each operator compresses what would otherwise be a tedious chain of comparisons into a single, declarative statement that both humans and the query optimizer can reason about efficiently.

SECTION 3

Visual Explanation — How Rows Are Filtered

The diagram shows how a five-row employees table is independently filtered by three different operators. The IN filter selects departments matching a set, BETWEEN selects salaries in a numeric range, and LIKE selects names matching a string pattern.

Notice that each operator functions as an independent Boolean predicate that is evaluated per row. When multiple operators appear in a single WHERE clause connected by AND, a row must satisfy every predicate to appear in the result. When connected by OR, satisfying any single predicate is sufficient. This compositional nature is one of the key strengths of SQL's declarative filtering model — you describe what rows you want, and the optimizer determines how to retrieve them.

SECTION 4

How Each Operator Works — Syntax and Semantics

The IN Predicate

IN — SYNTAX

column IN (value₁, value₂, …, valueₙ)

Equivalent to: column = value₁ OR column = value₂ OR … OR column = valueₙ. The list may also be a subquery that returns a single column.

The IN predicate performs a set membership test. Internally, most query optimizers convert a small literal list into a hash lookup or a sorted array with binary search, achieving O(1) or O(log n) per-row cost rather than the O(n) of naive sequential OR evaluation. When the list is a subquery, the optimizer may choose between a semi-join, a hash-join, or materializing the subquery into a temporary structure. The negated form, NOT IN, has a critical subtlety: if the list contains a NULL, the entire predicate evaluates to UNKNOWN for every row, effectively returning zero rows. This is because x <> NULL is UNKNOWN, and UNKNOWN ANDed with any other truth value can never produce TRUE.

The BETWEEN Predicate

BETWEEN — SYNTAX

column BETWEEN low AND high

Equivalent to: column >= low AND column <= high. Both endpoints are inclusive. If low > high, the result set is always empty.

The BETWEEN predicate is syntactic sugar for a double-bounded range comparison. It works with any data type that supports ordering: integers, decimals, dates, timestamps, and even strings (compared lexicographically under the column's collation). When an index exists on the filtered column, BETWEEN translates directly into an index range scan — one of the most efficient access patterns in relational engines. A common mistake is forgetting the inclusive semantics; if you need an exclusive upper bound (common with dates), you must rewrite the condition manually as column >= low AND column < high.

The LIKE Predicate

LIKE — SYNTAX

column LIKE 'pattern' [ESCAPE 'escape_char']

Wildcards: % matches zero or more characters; _ matches exactly one character. All other characters are matched literally.

The LIKE predicate implements simple pattern matching on character strings. Unlike full regular expressions, LIKE uses only two wildcard metacharacters, which makes it straightforward but limited in expressiveness. A leading % prevents the optimizer from using a B-tree index because the prefix is unknown — this is the so-called leading wildcard performance problem. A pattern like 'Joh%' can leverage an index because the fixed prefix 'Joh' narrows the range scan. Case sensitivity is determined by the column's or database's collation; use UPPER() or LOWER() wrappers for case-insensitive matching on case-sensitive collations, or use the vendor-specific ILIKE (PostgreSQL) operator.

⚠

NULL Pitfall with NOT IN

Consider SELECT * FROM t WHERE x NOT IN (1, 2, NULL). This expands to x<>1 AND x<>2 AND x<>NULL. Since x<>NULL is always UNKNOWN, the entire conjunction is never TRUE, so zero rows are returned. Prefer NOT EXISTS when NULLs may be present.

SECTION 5

Wildcard Classification & Pattern Matching Deep Dive

Understanding wildcards is the key to wielding LIKE effectively. The two standard SQL wildcards are simple in isolation, but their combinations create powerful filter expressions. Beyond standard LIKE, many database systems offer extended pattern matching through regular expression operators. This section classifies common patterns, shows their equivalences, and introduces vendor extensions.

This reference shows the two SQL standard wildcards (% and _), seven common pattern templates, and vendor-specific regex extensions for PostgreSQL, MySQL, and SQL Server.

When choosing between LIKE and a vendor-specific regex operator, consider portability versus expressiveness. Standard LIKE is supported identically on every SQL engine and is sufficient for prefix, suffix, and substring searches. However, it cannot express character classes (e.g., 'any digit'), alternation, or quantifiers. If you need to match a pattern like 'a phone number in the format (###) ###-####', you will either need multiple nested conditions with LIKE and underscore wildcards, or a single concise regex expression. The trade-off is that regex patterns are not indexable in most engines and may introduce portability issues when migrating between database systems.

SECTION 6

Worked Example — Filtering a Product Catalog

Suppose you manage a products table with columns product_id, category, price, and product_name. The business requirement is: "List all products that are in the Electronics, Books, or Toys categories, priced between $10 and $100, and whose name starts with 'Pro'."

Building a Compound Filter with IN, BETWEEN, and LIKE

1
Step 1 — Identify the Set Membership Filter

The requirement specifies three valid categories. We use the IN predicate to test set membership: category IN ('Electronics', 'Books', 'Toys'). This is equivalent to three OR-ed equality checks but is far more readable and often more efficiently optimized.

category IN ('Electronics', 'Books', 'Toys')
2
Step 2 — Apply the Range Filter

The price constraint is an inclusive range from $10 to $100. We use the BETWEEN predicate: price BETWEEN 10 AND 100. Remember that BETWEEN is inclusive on both ends, so products priced at exactly $10 or $100 will be included.

price BETWEEN 10 AND 100
3
Step 3 — Apply the Pattern Match Filter

Names starting with 'Pro' are matched using a prefix pattern with the percent wildcard: product_name LIKE 'Pro%'. Because the wildcard is at the end (not the beginning), a B-tree index on product_name can be leveraged for this filter.

product_name LIKE 'Pro%'
4
Step 4 — Compose the Full Query

Combine all three predicates with AND to require that every condition is satisfied simultaneously. The complete query is:

SELECT product_id, product_name, category, price FROM products WHERE category IN ('Electronics', 'Books', 'Toys') AND price BETWEEN 10 AND 100 AND product_name LIKE 'Pro%' ORDER BY price;
5
Step 5 — Verify with Sample Data

Given the sample rows: ('ProMax Headphones', 'Electronics', 79.99), ('ProBasic Pen', 'Office', 2.99), ('Pro Reader', 'Books', 24.50), ('ProBot', 'Toys', 149.99) — the query returns row 1 (Electronics, price in range, starts with Pro) and row 3 (Books, price in range, starts with Pro). Row 2 fails the category filter and row 4 fails the price filter.

Result: 2 rows returned — 'ProMax Headphones' ($79.99) and 'Pro Reader' ($24.50).

SECTION 7

Strengths, Limitations & Performance Considerations

Operator	Strengths	Limitations	Index Usage
IN	Concise set membership; supports subqueries; optimizer can hash the list for O(1) lookup.	NOT IN with NULLs yields empty results; very large literal lists (>10,000) may cause parse overhead.	Yes — index scan or seek when list is small; may switch to full scan for very large lists.
BETWEEN	Readable range check; maps directly to index range scan; works with dates, numbers, strings.	Always inclusive on both ends; cannot express exclusive bounds; can be confusing with timestamp precision.	Excellent — direct range scan on B-tree index.
LIKE	Simple, portable pattern matching; prefix patterns are indexable; ESCAPE clause handles literal wildcards.	Only two wildcards (limited expressiveness); leading '%' prevents index use; case sensitivity varies by collation.	Prefix pattern: index range scan. Leading '%': full table/index scan.

Comparison of IN, BETWEEN, and LIKE operators across key dimensions.

⚡

PERFORMANCE INSIGHT

Think of index usage like looking up a word in a physical dictionary. BETWEEN is like knowing the word starts with a letter range — you open the dictionary to 'M' and stop at 'P'. A prefix LIKE pattern works the same way: 'Pro%' sends you straight to the 'Pro' section. But a leading-wildcard LIKE pattern like '%son' is like searching for any word that ends with 'son' — the dictionary's alphabetical order is useless, and you must read every single page. For such patterns, consider full-text indexes (tsvector in PostgreSQL, FULLTEXT in MySQL) or trigram indexes (pg_trgm) that index substrings rather than prefixes.

SECTION 8

Connection to Advanced Filtering Techniques

The IN, BETWEEN, and LIKE operators form the foundation of SQL filtering, but real-world applications often demand more powerful constructs. Understanding how these basic operators relate to their advanced counterparts prepares you for complex query design and cross-platform work.

Basic Operator	Advanced Alternative	When to Upgrade
`IN (list)`	`EXISTS (subquery)`	When the subquery may return NULLs; when the subquery is correlated and you need row-by-row existence checks; when the subquery returns a large number of rows (EXISTS short-circuits).
`NOT IN`	`NOT EXISTS / LEFT JOIN ... IS NULL`	Always prefer NOT EXISTS or anti-join when NULLs are possible. NOT IN with NULLs silently returns zero rows — NOT EXISTS handles NULLs correctly.
`BETWEEN`	`Window functions / RANGE frames`	When computing rolling aggregates over a range of values (e.g., moving averages). BETWEEN within ROWS/RANGE BETWEEN in window function frame specifications.
`LIKE '%term%'`	Full-Text Search (FTS)	When searching natural language text across large datasets. FTS uses inverted indexes for sub-millisecond searches that LIKE '%..%' would need full scans for.
`LIKE pattern`	REGEXP / SIMILAR TO / ~ operator	When you need character classes, alternation, quantifiers, or anchoring beyond what '%' and '_' provide (e.g., validating email format).

Mapping basic operators to their advanced counterparts.

As you progress to topics like subqueries, CTEs, window functions, and full-text search, you will find that the declarative spirit of IN, BETWEEN, and LIKE — specifying what to filter rather than how to filter — remains the guiding principle. These basic operators remain the most commonly used filtering tools in production SQL, appearing in the vast majority of application queries. Mastering them thoroughly, including their edge cases with NULLs and their performance characteristics, is prerequisite knowledge for every database-related role in software engineering.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why SELECT * FROM orders WHERE status NOT IN ('shipped', 'delivered', NULL) returns zero rows, even if there are orders with status 'pending'. What alternative construct avoids this issue?

Show answer

The NOT IN expansion yields: status <> 'shipped' AND status <> 'delivered' AND status <> NULL. The comparison status <> NULL evaluates to UNKNOWN under SQL's three-valued logic, and UNKNOWN ANDed with any other truth value can never produce TRUE. Therefore, the WHERE clause is never satisfied and zero rows are returned. The idiomatic fix is to use NOT EXISTS with a correlated subquery, or to explicitly filter NULLs from the list: NOT IN (SELECT val FROM t WHERE val IS NOT NULL). Alternatively, a LEFT JOIN anti-pattern (LEFT JOIN … WHERE joined.id IS NULL) also handles NULLs correctly.

Question 2: BASIC CALCULATION

Write a query against a students table to find all students whose gpa is between 3.0 and 3.7 (inclusive) and whose major is one of 'CS', 'Math', or 'Physics'.

Show answer

SELECT * FROM students WHERE gpa BETWEEN 3.0 AND 3.7 AND major IN ('CS', 'Math', 'Physics'); The BETWEEN predicate is inclusive on both ends, so students with a gpa of exactly 3.0 or 3.7 are included. The IN predicate tests membership in the set {'CS', 'Math', 'Physics'}. Both predicates are combined with AND, requiring each row to satisfy both conditions. If the table has indexes on gpa or major, the optimizer may choose an index range scan on gpa or an index lookup on major, depending on selectivity estimates.

Question 3: INTERMEDIATE

Given a logs table with columns log_id, message, and created_at (TIMESTAMP), write a query that returns all log entries from January 2025 whose message contains the substring 'ERROR' and whose log_id is not in the set {100, 200, 300}. Discuss whether BETWEEN is appropriate for the date filtering.

Show answer

SELECT * FROM logs WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01' AND message LIKE '%ERROR%' AND log_id NOT IN (100, 200, 300); Using BETWEEN '2025-01-01' AND '2025-01-31' is risky with timestamps because BETWEEN is inclusive — it would miss entries on 2025-01-31 after midnight (e.g., 2025-01-31 08:30:00). The half-open interval pattern (>= start AND < next_month_start) is the standard best practice for timestamp range queries. The LIKE '%ERROR%' pattern requires a full scan of the message column because of the leading wildcard — for high-volume log tables, a full-text index would be more performant. The NOT IN is safe here because the literal list contains no NULLs.

Question 4: APPLIED

You are building a product search feature for an e-commerce application. The products table has columns sku (VARCHAR), name (VARCHAR), and price (DECIMAL). SKUs follow the pattern: two uppercase letters, a hyphen, four digits, a hyphen, and one uppercase letter (e.g., 'AB-1234-X'). Write a query using LIKE to find all products whose SKU matches this format and whose price is between $25 and $500. Can standard LIKE fully validate this pattern?

Show answer

SELECT sku, name, price FROM products WHERE sku LIKE '__-____-_' AND price BETWEEN 25 AND 500; This query uses nine characters in the LIKE pattern: two underscores (any char), a literal hyphen, four underscores, a literal hyphen, and one underscore. However, standard LIKE cannot validate that the first two positions are uppercase letters or that the middle four are digits — it only checks the total length and hyphen positions. To fully validate the format, you would need a regex: in PostgreSQL, sku ~ '^[A-Z]{2}-[0-9]{4}-[A-Z]

'; in MySQL, sku REGEXP '^[A-Z]{2}-[0-9]{4}-[A-Z]

'. SQL Server's LIKE extension supports bracket character classes: sku LIKE '[A-Z][A-Z]-[0-9][0-9][0-9][0-9]-[A-Z]', but this is non-standard. The BETWEEN clause works correctly here for decimal prices and is inclusive on both ends.

Question 5: CRITICAL THINKING

A colleague writes the following query and complains that it is slow on a table with 10 million rows:

SELECT * FROM customers WHERE last_name LIKE '%smith%' OR email LIKE '%@gmail.com' OR customer_id IN (SELECT customer_id FROM vip_list)

. There is a B-tree index on last_name, email, and customer_id. Analyze why the query is slow and propose at least three optimization strategies.

Show answer

The query is slow for several compounding reasons. First, both LIKE patterns have leading wildcards ('%smith%' and '%@gmail.com'), which prevent B-tree index usage on last_name and email — the engine must perform a full table scan for each. Second, the OR connective means the optimizer likely cannot use a single index efficiently; it may perform a full scan and evaluate all three conditions per row. Third, the IN subquery on vip_list adds a dependent lookup per row unless the optimizer converts it to a semi-join. Optimization strategies: (1) Replace '%smith%' with a trigram index (CREATE INDEX idx_trgm ON customers USING GIN(last_name gin_trgm_ops) in PostgreSQL) or a full-text index, enabling substring searches without full scans. (2) Rewrite '%@gmail.com' as a suffix search — reverse-function index (CREATE INDEX idx_rev_email ON customers(REVERSE(email))) and rewrite as REVERSE(email) LIKE REVERSE('%@gmail.com'), which becomes a prefix scan. Alternatively, extract the domain into a separate indexed column. (3) Replace the IN subquery with an EXISTS or a JOIN to vip_list, and consider UNION ALL to split the three OR branches into separate indexed queries whose results are merged, allowing each branch to use its own optimal access path. (4) If the table is read-heavy, consider materialized views or covering indexes tailored to this query pattern.

SUMMARY

Lesson Summary

Summary

The IN operator tests whether a column value belongs to a specified set of values or subquery result, replacing verbose chains of OR-ed equalities. The BETWEEN operator performs an inclusive range check on ordered data types — numbers, dates, and strings — and maps efficiently to B-tree index range scans. The LIKE operator enables pattern matching with two wildcards: % (zero or more characters) and _ (exactly one character), and prefix patterns are indexable while leading-wildcard patterns are not.

Critical pitfalls include the NOT IN NULL trap (where a single NULL in the list causes zero results), BETWEEN's inclusive boundary semantics (especially problematic with timestamps), and the leading wildcard performance penalty with LIKE. These operators form the foundation for all SQL filtering and connect to advanced techniques including EXISTS/NOT EXISTS, regular expressions, and full-text search.