Skip to main content
Filtering & Logic
2 / 4
0%

IN, BETWEEN, LIKE

0/4 solved4 to unlock next
PrevLocked

Lesson 02

IN, BETWEEN, LIKE

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.

Shorter filters for everyday questions

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:

  • "Orders that are pending or shipped or on hold" (one column, a list of allowed values)
  • "Products priced from 40 to 60" (one column, a range)
  • "Doctors whose specialty mentions oncology" (text that matches a pattern)

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.

Syntax

-- 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

IN: match any value in a list

Example 1: open orders

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.

Example 2: NOT IN for exclusions

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.

BETWEEN: inclusive ranges

Example 3: a price band

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.

Example 4: date ranges

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: pattern matching

LIKE compares text against a pattern that contains two wildcards:

WildcardMatchesExampleMatches 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'.

Example 5: starts with, contains, exact length

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.

Case sensitivity and ILIKE

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.

Matching a literal % or _

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 '!'.

How PostgreSQL evaluates it

  • 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.
  • Index use: a B-tree index on a text column can help with an anchored pattern like '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.

Common mistakes

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%'.

Other databases

FeaturePostgreSQLMySQLSQL ServerSQLite
IN, BETWEENstandardstandardstandardstandard
LIKE case-sensitive?yesno with default collationsno with default collationsno for ASCII letters
ILIKEyesnonono

In MySQL, SQL Server and SQLite you usually get case-insensitive LIKE by default; to force it portably, compare LOWER(column) LIKE 'pattern'.

Practice

The exercises below this lesson ask you to:

  • Find mid-priced products in a price range (remember whether the bounds are inclusive).
  • Find HR employees whose first name starts with a given letter.
  • Find hospital patients whose first name starts with one of two letters, sorted.
  • Filter products to a list of category ids using IN, sorted by two columns.

Key takeaways

  • 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.
  • PostgreSQL's LIKE is case-sensitive; use ILIKE to ignore case.
  • For timestamps, prefer >= start AND < next_start over BETWEEN.

IN, BETWEEN, LIKE: in depth

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.

Worked examples

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.

Common mistakes

  • Using BETWEEN on timestamps: BETWEEN '2024-01-01' AND '2024-01-31' misses everything after midnight on the 31st. Use >= and < instead.
  • NOT IN with a NULL in the list returns no rows at all, because every comparison becomes unknown.
  • A leading wildcard (LIKE '%son') cannot use a normal index, so it scans the whole table on large data.

Other databases

  • ILIKE exists only in PostgreSQL; elsewhere use LOWER(col) LIKE LOWER(pattern).
  • MySQL's default collation makes LIKE case-insensitive already; PostgreSQL's LIKE is case-sensitive.
Check yourself: Does 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

Quick check: IN, BETWEEN, LIKE

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Mid-priced products

Commerce Operations IntelligenceeasyPostgreSQL

From the products table (store sandbox), return name and price for products priced between 30 and 80 inclusive.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

First names starting with A

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name and last_name of HR employees whose first name starts with the letter A.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Patient names starting with C or D

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return first_name and last_name of patients whose first_name starts with 'C' or 'D', ordered by first_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Products in categories 1, 2, or 3

Commerce Operations IntelligenceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Filtering & Logic
2 / 4
0%

IN, BETWEEN, LIKE

0/4 solved4 to unlock next
PrevLocked