Lists, ranges and patterns

Problem

Three cities, three ORs

The courier company for the north-east and the coast serves Varna, Burgas and Ruse. You need the customers from those cities:

SQLdatabase: shop

SELECT name, city FROM customers
WHERE city = 'Varna' OR city = 'Burgas' OR city = 'Ruse';

SELECT name, city FROM customers WHERE city = 'Varna' OR city = 'Burgas' OR city = 'Ruse';

▶ RunRestore the queryFresh database

It works, but with ten cities the condition becomes a line of ORs where it is easy to miss one.

The idea

IN: one from the list

IN checks whether a value is in a list:

SQLdatabase: shop

SELECT name, city FROM customers
WHERE city IN ('Varna', 'Burgas', 'Ruse');

SELECT name, city FROM customers WHERE city IN ('Varna', 'Burgas', 'Ruse');

▶ RunRestore the queryFresh database

The same result, and the list reads at a glance. NOT IN is the opposite: everything except the listed ones.

The idea

BETWEEN: between two limits

BETWEEN a AND b means “from a to b”, including a and b:

SQLdatabase: shop

SELECT name, price FROM products WHERE price BETWEEN 2 AND 3;

SELECT name, price FROM products WHERE price BETWEEN 2 AND 3;

▶ RunRestore the queryFresh database

Dates in the database are text like 2026-03-31. Such text sorts the way dates do, so BETWEEN works for them too. The orders from the first quarter of 2026:

SQLdatabase: shop

SELECT id, ordered_at, status FROM orders
WHERE ordered_at BETWEEN '2026-01-01' AND '2026-03-31';

SELECT id, ordered_at, status FROM orders WHERE ordered_at BETWEEN '2026-01-01' AND '2026-03-31';

▶ RunRestore the queryFresh database

More precisely, if you are curious

If the date also has a time (2026-03-31 18:20), BETWEEN '2026-01-01' AND '2026-03-31' misses 31 March after midnight, because '2026-03-31 18:20' comes after '2026-03-31'. Then it is safer to write ordered_at >= '2026-01-01' AND ordered_at < '2026-04-01'.

Try it

The open orders

Show the number, date and status of the orders that are neither delivered (delivered) nor cancelled (cancelled).

SQLdatabase: shop

SELECT id, ordered_at, status FROM orders;

SELECT id, ordered_at, status FROM orders;

Check▶ Run

The idea

LIKE: by part of the text

LIKE compares with a pattern. In the pattern, % means “any text, even none”, and _ means “exactly one character”.

SQLdatabase: shop

SELECT name, price FROM products WHERE name LIKE '%bread%';

SELECT name, price FROM products WHERE name LIKE '%bread%';

▶ RunRestore the queryFresh database

'%bread%' finds every name with “bread” somewhere in it. 'bread%' would look for names that start with “bread”, and '%bread' for names that end with it.

See it

Upper and lower case

Which products start with “b”? Run:

SQLdatabase: shop

SELECT name FROM products WHERE name LIKE 'b%';

SELECT name FROM products WHERE name LIKE 'b%';

▶ RunRestore the queryFresh database

“Butter roll”, “Butter” and “Bananas” come out, although they start with a capital B. In SQLite LIKE ignores case, but only for Latin letters. For Cyrillic, a capital letter and its small form are two different characters: on the Bulgarian pages of this course, a search for names starting with a small Cyrillic “b” finds nothing.

More precisely, if you are curious

Databases differ here. In PostgreSQL LIKE always cares about case, and ILIKE does not, for any alphabet. In MySQL it depends on the text comparison setting (collation), and usually case is ignored. If a search has to work for every alphabet, SQLite needs an extension (ICU), or you keep a separate column with the text in lower case.

Try it

The milk

Show the name and price of the products with “milk” in the name.

SQLdatabase: shop

Check▶ Run

Exercise

Two tasks

1. The orders from summer 2026 (June, July, August): number, customer and date.

The orders between 1 June and 31 August 2026:

SQLdatabase: shop

Check▶ Run

2. The customers whose name starts with “M”: name and city.

The customers whose name starts with “M”:

SQLdatabase: shop

SELECT name, city FROM customers WHERE name = 'M';

SELECT name, city FROM customers WHERE name = 'M';

Check▶ Run

Test

Check yourself

1. Which prices are in price BETWEEN 2 AND 3?

Only 2 and 3From 2 to 3, including 2 and 3From 2 to 3, without 2 and 3 themselves

2. What does name LIKE '%coffee%' find?

Anything that starts with “coffee”Anything with “coffee” somewhere in itOnly “coffee”

3. In SQLite, does LIKE 'coffee%' find “Coffee beans”?

No: LIKE always cares about caseOnly with ILIKEYes: for Latin letters LIKE ignores case

4. How do you write “the date is in October 2026” for ordered_at?

ordered_at = '2026-10'ordered_at IN ('2026-10')ordered_at BETWEEN '2026-10-01' AND '2026-10-31'

Check

Start the lesson over