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 (σ).
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?
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
BETWEEN — Inclusive Range
LIKE — Pattern Matching
ESCAPE — Literal Wildcards
NULL Awareness
Visual Explanation — How Rows Are Filtered
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.
How Each Operator Works — Syntax and Semantics
The IN Predicate
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
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
% 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.
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.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.
% 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.
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'."
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')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 100product_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%'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;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. |
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). |
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.
Practice Problems
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?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'.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.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?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.Lesson 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.