WHERE Deep Dive ② — BETWEEN and LIKE for Range and Pattern Filtering

Data we'll use — the staff table

The second WHERE deep-dive article covers range filtering (BETWEEN) and pattern matching (LIKE). Combine the compound conditions you built last time with AND / OR / NOT with BETWEEN / LIKE from this article and you'll be able to express most real-world filter requirements in a single WHERE.

Before the exercises, confirm the staff table's column definitions and a sample of the data.

① Run PRAGMA table_info(staff); to check column names, types, and primary keys.

② Run SELECT * FROM staff LIMIT 5; to preview the first 5 rows.

BETWEEN — filter by value range

When you want to test whether a value falls inside a range — "salary between 5,000,000 and 6,000,000," "birthday between April 2020 and March 2025" — BETWEEN is the operator.

Writing column BETWEEN value1 AND value2 returns rows where the column is at least value1 and at most value2. Note that both endpoints are included. It means the same as column >= value1 AND column <= value2; people pick BETWEEN often just for readability.

Adding NOT BETWEEN returns the rows outside the range (less than value1 or greater than value2).

-- Staff with salary between 5,000,000 and 6,000,000 (endpoints included)
SELECT name, salary FROM staff
WHERE salary BETWEEN 5000000 AND 6000000;

-- Same meaning, written with >= and <= AND'd together
SELECT name, salary FROM staff
WHERE salary >= 5000000 AND salary <= 6000000;

-- Outside the range (under 5,000,000 OR over 6,000,000)
SELECT name, salary FROM staff
WHERE salary NOT BETWEEN 5000000 AND 6000000;

BETWEEN includes both endpoints

BETWEEN 5000000 AND 6000000 includes exactly 5,000,000 and exactly 6,000,000. If you want to exclude the endpoints, write > 5000000 AND < 6000000.

With dates, you can write BETWEEN '2020-04-01' AND '2025-03-31' — the literals get compared as strings. To include "all of March 2025," defining both endpoints with the actual end-of-month date is the safe approach. BETWEEN '2020-04-01' AND '2025-04-01' would also include `2025-04-01` itself, which is usually not what you want.

Imagine a leadership meeting needs the count of "mid-tier earners (salary 5,000,000 – 6,000,000)." (Once you run it correctly, the explanation will appear.)

① From staff, pull the name and salary columns.

② Filter to rows where `salary` is at least 5,000,000 and at most 6,000,000 (endpoints included).

③ Confirm the result is 3 rows (Bob 5,200,000 / Grace 5,500,000 / Jack 5,900,000).

Imagine a request to "see the staff outside the mid-tier (4,500,000 – 6,000,000), sorted by highest salary first."

① From staff, pull the name and salary columns.

② Filter to rows where `salary` is outside the range 4,500,000 – 6,000,000 (i.e., under 4,500,000 or over 6,000,000).

③ Sort by `salary` descending.

④ Confirm the result is 6 rows, starting with Frank 7,200,000 and ending with Emi 3,800,000.

LIKE — filter by pattern matching

When you want to filter by a string pattern — "names ending with Tanaka," "emails containing @example.com" — LIKE is the operator. Write column LIKE 'pattern'. Inside the pattern you can use the following two wildcards.

'A%' is starts with A, '%a' is ends with a, '%a%' is contains a, 'A___%' is starts with A and has at least three more characters — combine them as needed. Adding NOT LIKE returns rows that don't match the pattern.

-- 1) Prefix match: name starts with A
SELECT name FROM staff WHERE name LIKE 'A%';

-- 2) Suffix match: surname Tanaka
SELECT name FROM staff WHERE name LIKE '%Tanaka';

-- 3) Contains: name contains 'a' anywhere
SELECT name FROM staff WHERE name LIKE '%a%';

-- 4) Single character: 2nd char is 'a', the rest is anything
SELECT name FROM staff WHERE name LIKE '_a%';

-- 5) Negation: doesn't end with Tanaka
SELECT name FROM staff WHERE name NOT LIKE '%Tanaka';

LIKE and `=` follow different case-sensitivity rules

In this course's console (SQLite), within ASCII, LIKE is case-insensitive. name LIKE '%a%' and name LIKE '%A%' return the same rows. To make it case-sensitive, run PRAGMA case_sensitive_like = 1; or normalize both sides with LOWER(...).

Meanwhile, string comparison with `=` (covered in the SELECT article) is case-sensitive — watch for the difference in behavior between = and LIKE.

Searching for literal `%` or `_` — the `ESCAPE` clause

If the string you're searching for actually contains a literal % or _ (say, a product code like 'A_001'), then by default the _ will be interpreted as a wildcard. The ESCAPE clause lets you specify an escape character, after which % / _ are treated as literal characters.

Example: WHERE code LIKE 'A\_001' ESCAPE '\'; (declares \ as the escape character, so \_ is treated as a literal underscore). The escape character doesn't have to be \ — ESCAPE '#' makes # the escape, ESCAPE '!' makes it !, etc. You'll reach for this when searching code columns that contain _ or %.

Run the three patterns — prefix, suffix, contains-via-_ — in a single console and compare the results.

① For each query, pull name, city, and salary from staff.

② Pull rows where `name` starts with `A` (prefix match 'A%').

③ Then pull rows where `name` ends with `Tanaka` (suffix match '%Tanaka').

④ Then pull rows where the 2nd character of `name` is `a` ('_a%', where _ is any single character).

⑤ Confirm the row counts are 1 / 4 / 3.

Combining WHERE, ORDER BY, and LIMIT

Real-world queries usually combine filtering (WHERE) → sorting (ORDER BY) → row limit (LIMIT) in that exact order. The clause order is fixed, and swapping them around is a syntax error.

The DB's execution order is roughly FROM → WHERE → SELECT → ORDER BY → LIMIT — first narrow rows, pick the columns you need, sort them, then slice off the count you want.

Imagine a dashboard showing a salary ranking for "staff with a in their name."

① From staff, pull the name and salary columns.

② Filter to rows where `name` contains `a` (contains match).

③ Sort by `salary` descending, and take only the first 3 rows.

④ Confirm the result is 3 rows: Frank Tanaka 7,200,000 / David Sato 6,800,000 / Henry Sato 6,100,000.