BETWEEN, IN, LIKE

Handy operators for conditions

The restorers of old Earth could reconstruct a book from a fragment of its cover design. They didn't always need the exact title. Sometimes a single clue was enough: the first letter of the title, the section it belonged to, or a range of publication dates.

A data archivist works much the same way.

In WHERE, a long chain of conditions joined by AND and OR can be cumbersome. A dedicated operator often expresses the same idea more clearly:

price >= 1000 AND price <= 3000

can be written like this:

price BETWEEN 1000 AND 3000

Likewise, a long chain like:

category = 'Книги'
OR category = 'Игрушки'
OR category = 'Аксессуары'

can be replaced with:

category IN ('Книги', 'Игрушки', 'Аксессуары')

To find products whose name starts with the text "Coffee", you can use a pattern:

name LIKE 'Кофе%'

This lesson has three main tools:

• BETWEEN a AND b — checks whether the value falls within the range from a to b;
• IN (...) — checks whether the value appears in the list;
• LIKE 'pattern' — checks whether the text matches a pattern.

None of the three operators does anything magical. They simply help you express conditions more concisely and clearly.

QUERY: A good filter is like a precise order to a scout drone: not "go find something useful," but "show me products from these categories, in this price range, with names that start like this."

Three tools for three kinds of search

These operators have different jobs.

BETWEEN answers the question:

Does the value fall between two bounds?

For example:

price BETWEEN 1000 AND 3000

It reads as:

The price is between 1000 and 3000, inclusive.

IN answers the question:

Is the value one of these options?

For example:

category IN ('Книги', 'Игрушки')

It reads as:

The category is either Books or Toys.

LIKE answers the question:

Does the text match this pattern?

For example:

name LIKE 'Кофе%'

It reads as:

The name starts with "Coffee".

So:

BETWEEN: a value between two bounds

BETWEEN checks whether a value falls within a range.

Suppose you need products priced from 1000 to 3000:

SELECT name, price
FROM products
WHERE price BETWEEN 1000 AND 3000;

That's the same as writing:

SELECT name, price
FROM products
WHERE price >= 1000
  AND price <= 3000;

The key detail:

BETWEEN includes both bounds.

So the condition:

price BETWEEN 1000 AND 3000

will include products priced at:

• 1000
• 1500
• 2999
• 3000

A price of 1000 is included.
A price of 3000 is included too.

If you need values strictly greater than 1000 and strictly less than 3000, BETWEEN isn't the right tool. Use ordinary comparisons instead:

WHERE price > 1000
  AND price < 3000

Important details about BETWEEN

1. SQL won't reorder the bounds for you

This filter is fine:

WHERE price BETWEEN 1000 AND 3000

But this one will almost always return no rows:

WHERE price BETWEEN 3000 AND 1000

because the database reads it as:

WHERE price >= 3000
  AND price <= 1000

No number can be greater than or equal to 3000 and less than or equal to 1000 at the same time.

So the order of the bounds matters: put the lower one first, then the upper one.

2. Use NOT BETWEEN for the opposite

To find products outside the range, write:

SELECT name, price
FROM products
WHERE price NOT BETWEEN 1000 AND 3000;

That's similar to:

WHERE price < 1000
   OR price > 3000

So a product priced at 500 is included.
A product priced at 4000 is included.
A product priced at 1000 is not included.
A product priced at 3000 is not included.

The endpoints are excluded because they belong to the BETWEEN range itself.

3. With dates and times you need to be more careful

BETWEEN works with dates too:

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

But if created_at is a timestamp rather than a plain date, for example:

2024-01-31 18:45:00

then a filter like that may miss most of the last day.

Why? For a timestamp, '2024-01-31' often means the start of the day:

2024-01-31 00:00:00

That's why, for a period in time, it's often safer to write a half-open interval:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

This includes everything from January 1 up to, but not including, February 1.

For now, remember this:

For numbers, BETWEEN is convenient and clear. For timestamps, check the bounds especially carefully.

IN: the value is in a list

IN lets you test a value against several possible options.

Suppose you need products only from the "Books" and "Toys" categories:

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки');

That's shorter and clearer than:

SELECT name, category, price
FROM products
WHERE category = 'Книги'
   OR category = 'Игрушки';

IN reads like this:

The category is either Books or Toys.

IN is especially useful when there are many options:

WHERE category IN ('Книги', 'Игрушки', 'Аксессуары', 'Корм', 'Домики')

Instead of a long chain of OR you get one compact filter.

Important details about IN

1. IN is ideal for a list of exact values

IN doesn't look for similar values. It checks for an exact match with one of the options.

WHERE category IN ('Книги', 'Игрушки')

This will find the category 'Книги'.

But it won't find:

Книга
книги
КНИГИ
Электронные книги

because each is a different string.

2. NOT IN excludes values from the list

To exclude several categories, write:

SELECT name, category, price
FROM products
WHERE category NOT IN ('Книги', 'Игрушки');

It reads like this:

Show me products whose category is neither Books nor Toys.

But NOT IN has an important trap involving NULL. We'll cover it in a separate section below.

3. IN works well with BETWEEN

For example:

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000
ORDER BY price;

It reads like this:

Show me books and toys priced from 1000 to 3000.

That's a complete filter: a list of categories combined with a price range.

LIKE: searching by a text pattern

LIKE lets you match text against a pattern instead of an exact value.

Suppose you need products whose name starts with the text "Coffee":

SELECT name, price
FROM products
WHERE name LIKE 'Кофе%';

The % sign means:

Any sequence of characters.

That includes zero characters.

So the pattern:

'Кофе%'

will find:

Кофе
Кофе молотый
Кофейная станция
Кофе для котонавтов

but won't find:

Большой кофе
Набор: кофе и кружка

because the pattern requires the text to begin with Кофе.

%: any number of characters

% is the most commonly used wildcard with LIKE.

It means:

Any sequence of characters can appear here.

Let's compare a few patterns.

Starts with this text

WHERE name LIKE 'Кофе%'

Matches names that begin with Кофе.

Examples of matches:

Кофе
Кофе молотый
Кофейный набор

Ends with this text

WHERE name LIKE '%кофе'

Matches names that end with кофе.

Examples of matches:

Большой кофе
Капсульный кофе
Набор для кофе

Contains the text anywhere

WHERE name LIKE '%кофе%'

Matches names in which кофе appears anywhere.

Examples of matches:

кофе
Большой кофе
Набор для кофе и чая
Свежие кофейные зёрна

But the capitalized example from the first list won't appear here: LIKE is case-sensitive: К and к are different characters. We'll return to case sensitivity later in this lesson.

In short:

'Кофе%'

— the text must begin with that sequence.

'%кофе'

— the text must end with that sequence.

'%кофе%'

— the text must contain that sequence somewhere.

_: exactly one character

Besides %, LIKE has another wildcard:

_

It means:

Exactly one character.

For example:

WHERE code LIKE 'A_1'

These will match:

AB1
AX1
A71

but these won't:

A1
ABCD1
AA21

Why?

The pattern A_1 requires:

1. A first
2. then exactly one character
3. then 1

Another example:

WHERE name LIKE 'Кот_'

These four-character strings will match:

Коты
Котя
Кот1

but these won't:

Кот
Котик

They don't match because _ requires exactly one extra character.

Here's the difference:

Case: LIKE, ILIKE, and LOWER

In PostgreSQL, plain LIKE is case-sensitive.

That means the pattern:

WHERE name LIKE 'смарт%'

will not find this row:

Смарт-часы

because с and С are different characters.

To search regardless of case, PostgreSQL has ILIKE:

SELECT name, price
FROM products
WHERE name ILIKE 'смарт%';

ILIKE is the case-insensitive version of LIKE.

It will find:

смарт-часы
Смарт-часы
СМАРТ-ЧАСЫ

But keep in mind: ILIKE is a PostgreSQL-specific feature, not a universal part of standard SQL.

A more portable approach is to normalize the text to one case:

SELECT name, price
FROM products
WHERE LOWER(name) LIKE 'смарт%';

Here the database first converts name to lowercase, then compares it with a lowercase pattern.

You can also do it this way:

WHERE LOWER(name) LIKE LOWER('Смарт%')

but the pattern is usually written in lowercase from the start:

WHERE LOWER(name) LIKE 'смарт%'

How to search for the characters % and _ themselves

LIKE has a catch: % and _ are special characters.

% matches any sequence of characters.
_ means exactly one character.

But sometimes you need to match a literal percent sign or underscore.

For example, here are some products:

Скидка 50%
Корм 50 кг
QA_набор
QA-набор

If you write:

WHERE name LIKE '50%'

That doesn't mean "find the literal text 50%".

It means:

Find the rows that begin with 50, followed by anything at all.

To tell the database "this % is a literal percent sign", use ESCAPE.

For example:

SELECT name
FROM products
WHERE name LIKE '%50!%%' ESCAPE '!';

Let's break the pattern down:

'%50!%%'

• the first % — any sequence at the beginning of the string
• 50 — literal characters
• !% — a literal percent sign, because ! has been declared the escape character
• the last % — any sequence after it

The phrase:

ESCAPE '!'

tells SQL:

When ! appears before a % or a _, treat the next character literally.

You can look for an underscore the same way:

SELECT name
FROM products
WHERE name LIKE 'QA!_%' ESCAPE '!';

This pattern will find names that begin with the literal text QA_.

Without escaping, the pattern:

'QA_%'

would mean:

QA, then any one character, then any continuation.

So it could also match rows such as:

QA-набор
QA1набор
QA набор

QUERY: In a LIKE stencil, % and _ are openings, not markings. To search for those symbols themselves, tell the Archive to treat them as literal characters.

The cost of a leading %

On a small table, the difference may be barely noticeable. With millions of rows, however, different LIKE patterns can perform very differently.

Compare two conditions:

WHERE name LIKE 'Кофе%'

and

WHERE name LIKE '%кофе'

The first condition specifies the beginning of the string. The database can search much like you would use a paper dictionary:

Jump to that section and look nearby.

An index can sometimes speed up this kind of prefix search.

The second condition begins with %:

'%кофе'

which means:

Anything at all can come before that text.

Because the prefix is unknown, an ordinary index on the name column is far less useful: the database doesn't know where to start looking.

That's why queries of this shape:

WHERE name LIKE '%кофе%'

may require a broad scan: the database may have to inspect every name.

A simple way to think about it:

PostgreSQL has some technical nuances here. Depending on the locale, speeding up a prefix LIKE search may require an index that uses text_pattern_ops. Searching within a string usually requires trigram indexes, such as those provided by pg_trgm.

For now, focus on the main idea:

If a pattern begins with ordinary characters, the database has a useful starting point. If a pattern begins with %, the database often has to read far more of the table.

This ability to make effective use of an index is called sargability. You'll explore it in depth in the optimization module.

UNKNOWN in list conditions and the NOT IN trap

In the previous lesson, you saw that NULL produces a third logical result:

UNKNOWN

That matters for IN too.

When you write the list yourself and it contains only regular values, everything is straightforward:

WHERE category IN ('Книги', 'Игрушки')

This is roughly equivalent to:

WHERE category = 'Книги'
   OR category = 'Игрушки'

But NOT IN works like a chain of conditions joined by AND.

For example:

WHERE category NOT IN ('Книги', 'Игрушки')

is roughly equivalent to:

WHERE category <> 'Книги'
  AND category <> 'Игрушки'

This is where the trap lies.

Take a look:

SELECT 'нашлось'
WHERE 1 NOT IN (2, 3);

This query returns a row because:

1 <> 2 AND 1 <> 3

evaluates to:

TRUE AND TRUE

The overall result is TRUE.

Now consider this query:

SELECT 'нашлось'
WHERE 1 NOT IN (2, NULL);

This query returns no rows at all.

Why?

NOT IN (2, NULL) is roughly equivalent to:

1 <> 2 AND 1 <> NULL

The first part:

1 <> 2

evaluates to TRUE.

The second part:

1 <> NULL

evaluates to UNKNOWN.

So the full condition:

TRUE AND UNKNOWN

evaluates to UNKNOWN.

And WHERE keeps only rows for which the condition evaluates to TRUE.

So the row never reaches the result.

The real danger is not a list you write yourself—you can see any NULL values there.

The danger appears with subqueries:

WHERE product_id NOT IN (
    SELECT product_id
    FROM archived_products
)

If the subquery returns even a single NULL, the result may be unexpectedly empty.

For now, remember this warning sign:

NOT IN + a possible NULL = the risk of an empty result.

Later, when we cover subqueries, you’ll learn safer approaches using NOT EXISTS.

What happens when the value is NULL

BETWEEN, IN and LIKE follow the logic of NULL too.

If a value is missing, a regular condition does not evaluate to TRUE.

For example:

price BETWEEN 1000 AND 3000

If price is NULL, the result is UNKNOWN.

category IN ('Книги', 'Игрушки')

If category is NULL, the result is UNKNOWN.

name LIKE 'Кофе%'

If name is NULL, the result is UNKNOWN.

And WHERE keeps only rows for which the condition evaluates to TRUE.

So rows with NULL are filtered out unless you account for them explicitly.

If you need to include them, add an explicit condition:

WHERE price BETWEEN 1000 AND 3000
   OR price IS NULL

or:

WHERE category IN ('Книги', 'Игрушки')
   OR category IS NULL

Don’t assume that BETWEEN, IN or LIKE handles missing values automatically. You still need to handle NULL explicitly with IS NULL or IS NOT NULL.

Common mistakes

Mistake 1. Forgetting that BETWEEN includes the bounds

WHERE price BETWEEN 1000 AND 3000

This includes both 1000 and 3000.

If you want to exclude the bounds, write:

WHERE price > 1000
  AND price < 3000

Mistake 2. Using BETWEEN with dates and accidentally cutting off the last day

Be careful:

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

If created_at also stores a time, some rows from January 31 may fall outside the range.

It is often safer to write:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Mistake 3. Writing a long chain of OR conditions instead of IN

This works:

WHERE category = 'Книги'
   OR category = 'Игрушки'
   OR category = 'Аксессуары'

But this is clearer:

WHERE category IN ('Книги', 'Игрушки', 'Аксессуары')

Mistake 4. Thinking that IN performs pattern matching

WHERE category IN ('Книги')

will not find:

Электронные книги

because IN checks for an exact match.

To match a text pattern, use LIKE:

WHERE category LIKE '%книги%'

Or use ILIKE for a case-insensitive search in PostgreSQL:

WHERE category ILIKE '%книги%'

Mistake 5. Mixing up % and _

LIKE 'A%'

A, followed by any number of characters.

LIKE 'A_'

A, followed by exactly one character.

Mistake 6. Forgetting to escape % and _

If you need to find a literal % sign, don’t write it like this:

WHERE name LIKE '%50%%'

Instead, escape it explicitly:

WHERE name LIKE '%50!%%' ESCAPE '!'

Mistake 7. Putting % at the start of the pattern and expecting it to be fast

WHERE name LIKE '%кофе%'

A search like this may be fine on a small table, but it often becomes expensive on a large one.

QUERY: A range, a list, and a pattern—three quick ways to query the archive. Just remember when a pattern helps and when it forces the archive to scan everything from start to finish.