IN, BETWEEN, and LIKE

Level 1: Level 1: SQL Foundations (Reading Source Data)easy12 minINNOT INBETWEENLIKE% / _ wildcards

Match sets, ranges, and text patterns when filtering source rows.

Three shapes of filter

Beyond =, a data engineer constantly needs three more filter shapes:

Set membership means "status is one of these": status IN ('paid','shipped') (cleaner than a chain of ORs).
Range means "price in this band": unit_price_cents BETWEEN 1000 AND 5000 (inclusive on both ends).
Pattern means "SKU looks like this": sku LIKE 'AUD-%'. In LIKE, % matches any run of characters, _ matches exactly one.

Worked example

SELECT product_id, sku, category_code, unit_price_cents
FROM products
WHERE category_code IN ('AUD','HOM')
  AND unit_price_cents BETWEEN 2000 AND 9000
  AND sku LIKE 'SKU-%';

Anatomy

category_code IN ('AUD','HOM')          -- matches any value in the list
unit_price_cents BETWEEN 2000 AND 9000  -- 2000 <= x <= 9000 (both inclusive)
sku LIKE 'SKU-%'                         -- '%' = any chars, '_' = one char

The NOT IN + NULL trap

If the list inside NOT IN contains a NULL (or the column being tested is NULL), the result can become "unknown" and silently drop rows you expected to keep. status NOT IN ('paid', NULL) returns no rows at all. When you use NOT IN, make sure neither side involves NULLs, or switch to NOT EXISTS. (More on this in the next lesson.)

Pitfall

BETWEEN is inclusive: BETWEEN 1 AND 10 includes both 1 and 10. If you mean "under 10," don't use BETWEEN.

In the warehouse this differs… LIKE is case-insensitive for ASCII in SQLite by default but case-sensitive in Postgres. Normalize case first if it matters (see Lesson sql-l1-strings).

Recap

IN for sets, BETWEEN for inclusive ranges, LIKE with %/_ for patterns. Never put NULL near NOT IN.

Sample data for this example

CREATE TABLE products (
  product_id       INTEGER,
  sku              TEXT,
  category_code    TEXT,
  unit_price_cents INTEGER
);
INSERT INTO products VALUES
  (1, 'SKU-AUD-01', 'AUD', 2999),
  (2, 'SKU-HOM-05', 'HOM', 4500),
  (3, 'SKU-TOY-09', 'TOY', 1200),
  (4, 'SKU-AUD-02', 'AUD', 8900),
  (5, 'SKU-HOM-07', 'HOM', 15000);

Worked example (SQL)

SELECT product_id, sku, category_code, unit_price_cents
FROM products
WHERE category_code IN ('AUD','HOM')
  AND unit_price_cents BETWEEN 2000 AND 9000
  AND sku LIKE 'SKU-%';

Apply

Your turn

The task this lesson builds to.

Filter products to category codes in the set ('AUD','HOM') and a price band of 2000 to 9000 cents inclusive. Return product_id, category_code, unit_price_cents.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Quarantine suspect rows: return product_id, sku, status for products that match all three conditions:

the sku matches the malformed prefix pattern TMP-% (temporary SKUs that should never ship),
the status is in the excluded set ('draft','deprecated'),
the added_date is outside the valid window 2026-01-01 to 2026-02-28 (i.e., not BETWEEN those dates; ISO date text compares lexicographically, so BETWEEN works on 'YYYY-MM-DD').

3 hints and 1 automated check are waiting in the workspace.