IN, BETWEEN, LIKE, and Pattern Matching

Learning outcomes

SQL provides compact predicate families for membership, ranges, and patterns. Their convenience is valuable only when endpoint, NULL, case, and escaping rules are understood.

IN expresses membership

SELECT    product_id,    product_name,    categoryFROM productWHERE category IN ('book', 'lab');

For ordinary non-NULL values, this is equivalent to a chain of equality comparisons joined by OR.

WHERE category = 'book'   OR category = 'lab';

IN is easier to extend and makes membership intent explicit.

IN can use a subquery

SELECT    customer_id,    full_nameFROM customerWHERE customer_id IN (    SELECT customer_id    FROM sale);

The subquery supplies a set of candidate values. Later chapters compare this form with joins and EXISTS.

The NOT IN and NULL trap

SELECT customer_id, full_nameFROM customerWHERE customer_id NOT IN (2, 4, NULL);

Because comparison with NULL is UNKNOWN, the overall predicate may become UNKNOWN for every row. A NOT IN subquery is especially risky when the subquery column can contain NULL.

SELECT customer_id, full_nameFROM customerWHERE customer_id NOT IN (2, 4);-- Later-course pattern for a nullable subquery source:SELECT c.customer_id, c.full_nameFROM customer AS cWHERE NOT EXISTS (    SELECT 1    FROM sale AS s    WHERE s.customer_id = c.customer_id);

BETWEEN includes both endpoints

SELECT    product_id,    product_name,    unit_priceFROM productWHERE unit_price BETWEEN 24 AND 69;

The portable meaning is equivalent to:

WHERE unit_price >= 24  AND unit_price <= 69;

Timestamps favor half-open ranges

SELECT sale_id, sold_atFROM saleWHERE sold_at >= '2026-08-03 00:00:00'  AND sold_at <  '2026-08-05 00:00:00';

BETWEEN '2026-08-03' AND '2026-08-04' can fail to include timestamps later on August 4, depending on representation. Half-open ranges scale cleanly to fractional seconds and adjacent periods.

LIKE pattern symbols

SELECT sku, product_nameFROM productWHERE sku LIKE 'SQL%';SELECT sku, product_nameFROM productWHERE product_name LIKE '%Database%';

Escape literal percent and underscore

SELECT sku, product_nameFROM productWHERE sku LIKE 'DATA!_%' ESCAPE '!';

SELECT sku, product_nameFROM productWHERE product_name LIKE '%10!%%' ESCAPE '!';

The ESCAPE clause makes the chosen escape character explicit. This is clearer than relying on dialect defaults.

Case sensitivity varies by engine

SQLite’s default LIKE behavior is case-insensitive for ASCII characters but has important Unicode and configuration limitations. Other engines derive behavior from collations, data types, or separate operators such as PostgreSQL’s ILIKE.

Pattern shape affects index opportunities

-- Often more index-friendlyWHERE product_name LIKE 'Database%'-- Usually requires examining many valuesWHERE product_name LIKE '%Database%';

A leading wildcard removes the known beginning of the search value. Actual index use depends on the engine, collation, expression, statistics, and available indexes; verify with query plans later in the course.

Practice lab

1. Return products in the course or lab category.
2. Return products priced from 29 through 69, including both endpoints.
3. Return sales from August 3 through August 4 using a half-open range.
4. Return SKUs that begin with DB- or SQL-.
5. Return the product whose SKU contains a literal underscore.
6. Return customers who have no sales, using NOT EXISTS.

SELECT product_id, product_nameFROM productWHERE category IN ('course', 'lab');SELECT product_id, product_name, unit_priceFROM productWHERE unit_price BETWEEN 29 AND 69;SELECT sale_id, sold_atFROM saleWHERE sold_at >= '2026-08-03 00:00:00'  AND sold_at <  '2026-08-05 00:00:00';SELECT sku, product_nameFROM productWHERE sku LIKE 'DB-%'   OR sku LIKE 'SQL-%';SELECT sku, product_nameFROM productWHERE sku LIKE 'DATA!_%' ESCAPE '!';SELECT c.customer_id, c.full_nameFROM customer AS cWHERE NOT EXISTS (    SELECT 1    FROM sale AS s    WHERE s.customer_id = c.customer_id);

Common failures

Using NOT IN with a nullable list

One NULL can turn the result into UNKNOWN for all candidate rows.

Treating BETWEEN as exclusive

Both boundaries are included.

Using date-only upper bounds for timestamps

Prefer the next period’s start as an exclusive upper boundary.

Forgetting that underscore is a wildcard

Use an explicit escape character for literal wildcard symbols.

Assuming LIKE case behavior is portable

Design and document collation and Unicode requirements.

Summary and references

• IN expresses finite membership clearly.
• Guard NOT IN against NULL or use NOT EXISTS.
• BETWEEN includes both endpoints.
• LIKE uses percent and underscore wildcards; ESCAPE handles literals.
• Use half-open timestamp ranges and verify pattern-search plans.

References

• SQLite: LIKE, GLOB, REGEXP, MATCH, and extract operators
• SQLite: BETWEEN
• PostgreSQL: pattern matching