Lesson Description
Range and pattern matching.
Lesson 02
Lesson Description
Range and pattern matching.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
In the previous lesson you built filters out of AND, OR and NOT. They can express anything, but some patterns come up so often that writing them longhand gets tedious and error-prone:
SQL has a dedicated operator for each: IN for lists, BETWEEN for ranges and LIKE for patterns. They do not add new power, but they make your intent obvious at a glance and remove a whole class of parenthesis bugs.
-- list membership
WHERE column IN (value1, value2, value3)
WHERE column NOT IN (value1, value2)
-- inclusive range
WHERE column BETWEEN low AND high
WHERE column NOT BETWEEN low AND high
-- pattern matching on text
WHERE column LIKE 'pattern'
WHERE column NOT LIKE 'pattern'
WHERE column ILIKE 'pattern' -- PostgreSQL: case-insensitive
Business question: which store orders are still in progress?
SELECT id, customer_id, order_date, status
FROM orders
WHERE status IN ('pending', 'shipped');
This returns 14 rows (7 pending and 7 shipped). It is exactly equivalent to status = 'pending' OR status = 'shipped', but it scales better: adding a third status means adding one item to the list instead of another OR clause.
Because IN is a single condition, it also plays nicely with AND. You can write WHERE status IN ('pending', 'shipped') AND order_date >= '2025-05-01' without worrying about precedence. That returns 9 orders placed in May 2025 that have not arrived yet.
Marketing wants to target customers outside North America:
SELECT first_name, last_name, country
FROM customers
WHERE country NOT IN ('United States', 'Canada', 'Mexico');
The store has 22 customers: 2 in the United States, 3 in Canada and 2 in Mexico. That leaves 15 rows from Europe and Brazil. Notice the spelling: the data says 'United States', not 'USA'. A value that is not in the data never matches anything, so NOT IN ('USA') would quietly exclude nobody. When a filter returns more rows than you expect, check the real values with SELECT DISTINCT country FROM customers.
Business question: which products cost between 40 and 60?
SELECT name, price
FROM products
WHERE price BETWEEN 40 AND 60
ORDER BY price;
You get 6 products, from the Cast Iron Skillet (44.95) to Denim Jeans (59.99). BETWEEN low AND high means price >= low AND price <= high, so both endpoints are included. A product costing exactly 40.00 or 60.00 would be returned.
The order of the bounds matters. price BETWEEN 60 AND 40 returns 0 rows, because no number is both at least 60 and at most 40. PostgreSQL also offers BETWEEN SYMMETRIC 60 AND 40, which swaps the bounds for you and returns the same 6 rows, but it is rarely needed if you simply put the low value first.
BETWEEN works on any type that can be ordered, including dates. How many HR employees were hired during 2005?
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2005-01-01' AND '2005-12-31';
That returns 15 of the 40 employees, which tells you 2005 was a big hiring year. In the hospital sandbox, WHERE date_of_birth BETWEEN '1960-01-01' AND '1969-12-31' finds the 5 patients born in the 1960s.
This works cleanly because hire_date is a date column with no time part. See the common mistakes section for what changes with timestamps.
LIKE compares text against a pattern that contains two wildcards:
| Wildcard | Matches | Example | Matches values like |
|---|---|---|---|
% | any run of characters, including none | 'K%' | King, Kochhar, K |
_ | exactly one character | '_ing' | King, Ring (not Viking) |
Without wildcards, LIKE 'King' behaves like = 'King'.
Last names starting with K:
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'K%';
4 rows: King, Kochhar, Khoo and Kaufling. The pattern is anchored: 'K%' only matches when K is the first character.
To match text anywhere in the value, put % on both sides. In the hospital sandbox:
SELECT first_name, last_name, specialty
FROM doctors
WHERE specialty LIKE '%Onco%';
3 doctors: Aisha Khan and Tomás García (Oncologist) and Léa Dubois (Neuro-Oncologist). The leading % lets "Onco" appear after "Neuro-".
Underscores count characters. WHERE last_name LIKE '____' (four underscores) finds every employee whose last name is exactly four characters: King, Chen, Popp, Khoo and Hall, 5 rows.
In PostgreSQL, LIKE is case-sensitive. last_name LIKE 'k%' returns 0 rows, and specialty LIKE '%onco%' also returns 0, because every match in the data has a capital O. PostgreSQL's ILIKE ignores case:
SELECT first_name, last_name, specialty
FROM doctors
WHERE specialty ILIKE '%onco%';
This brings back the same 3 doctors. ILIKE is handy for search boxes where you cannot control how the user types, for example reason ILIKE '%follow-up%' finds 13 follow-up appointments regardless of capitalization.
If you need to find a real percent sign or underscore, escape it with a backslash, which is PostgreSQL's default escape character: '100%' LIKE '100\%' is true, while '1000' LIKE '100\%' is false. You can choose a different escape character with LIKE '100!%' ESCAPE '!'.
x IN (a, b, c) is rewritten as x = a OR x = b OR x = c. That is why it follows the same NULL rules as =: a NULL in x never matches, and a NULL inside a NOT IN list makes the whole condition unknown for every row. The next lesson shows that trap in detail.x BETWEEN a AND b is rewritten as x >= a AND x <= b. Nothing more.LIKE is implemented by the ~~ operator (and ILIKE by ~~*). You will see these names in error messages and EXPLAIN output.'K%' (given a suitable collation or the text_pattern_ops operator class), but a pattern starting with %, like '%Onco%', has to inspect every row. On large tables, the pg_trgm extension or full-text search are the usual answers.Mistake 1: putting the whole list in one string.
SELECT id FROM orders
WHERE status IN ('pending, shipped');
This returns 0 rows. The list has one item, the 16-character string pending, shipped, which matches no order. Fix: quote each value separately, IN ('pending', 'shipped').
Mistake 2: using LIKE on a number.
SELECT name FROM products
WHERE price LIKE '4%';
PostgreSQL refuses: ERROR: operator does not exist: numeric ~~ unknown. LIKE only works on text. If you really mean "the price, written out, starts with 4", cast it: price::text LIKE '4%' (4 products). Usually, though, you wanted a range: price >= 40 AND price < 50.
Mistake 3: BETWEEN with timestamps. For a date column, BETWEEN '2025-03-01' AND '2025-03-31' catches the whole of March (10 store orders, 14 hospital appointments). For a timestamp column, the upper bound '2025-03-31' means midnight at the start of March 31, so anything later that day is silently dropped. You can check this yourself: '2025-03-31 14:00'::timestamp BETWEEN '2025-03-01' AND '2025-03-31' is false. Fix: use a half-open range, >= '2025-03-01' AND < '2025-04-01', which is correct for both types.
Mistake 4: forgetting the wildcard. WHERE first_name LIKE 'A' only matches a first name that is exactly "A". You almost certainly meant 'A%'.
| Feature | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
IN, BETWEEN | standard | standard | standard | standard |
LIKE case-sensitive? | yes | no with default collations | no with default collations | no for ASCII letters |
ILIKE | yes | no | no | no |
In MySQL, SQL Server and SQLite you usually get case-insensitive LIKE by default; to force it portably, compare LOWER(column) LIKE 'pattern'.
The exercises below this lesson ask you to:
IN, sorted by two columns.IN (a, b, c) is shorthand for a chain of = checks joined by OR, and it avoids precedence bugs.BETWEEN low AND high includes both endpoints; the low value must come first.% matches any run of characters and _ matches exactly one; LIKE without wildcards is just equality.LIKE is case-sensitive; use ILIKE to ignore case.>= start AND < next_start over BETWEEN.IN, BETWEEN and LIKE are shorthand for patterns you would otherwise write with many ORs or comparisons: membership in a list, values in a range, and text that matches a pattern. They make filters shorter and easier to read.
Membership and ranges
SELECT first_name, department_id, salary
FROM employees
WHERE department_id IN (50, 60, 80)
AND salary BETWEEN 5000 AND 9000;IN replaces three OR conditions. BETWEEN is inclusive at both ends, so 5000 and 9000 themselves match.
Pattern matching on text
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'Mc%'
OR first_name ILIKE '%ann%';% matches any run of characters and _ matches exactly one. ILIKE is PostgreSQL's case-insensitive LIKE.
salary BETWEEN 9000 AND 5000 return anything?No. BETWEEN a AND b means >= a AND <= b, so a reversed range matches nothing. Put the lower bound first.
Concept check
3 quick questions. One at a time. Instant score at the end.
From the products table (store sandbox), return name and price for products priced between 30 and 80 inclusive.
No results yet
Run a query to see results here
Return first_name and last_name of HR employees whose first name starts with the letter A.
No results yet
Run a query to see results here
From the hospital sandbox, return first_name and last_name of patients whose first_name starts with 'C' or 'D', ordered by first_name.
No results yet
Run a query to see results here
From the store sandbox, return name, category_id, and price for products where category_id is 1, 2, or 3. Use IN. Order by category_id, then name.
No results yet
Run a query to see results here