Lists and ranges

What problem do IN and BETWEEN solve?

Two kinds of condition come up constantly: "is this value one of these?" and "is this value inside this range?". You can write both with OR and AND, but the result is long and easy to get wrong. IN and BETWEEN say the same thing in a few words.

This lesson uses a library's loans. Each loan records the member, the title, its genre, the date it was taken out, and days_kept, the number of days before it came back. Books still out have no days_kept yet, so that cell is NULL. One member, Femi, has no home branch recorded.

SELECT title, genre, loaned_on
FROM loans
ORDER BY loaned_on;

How does IN match a list of values?

genre IN ('Fiction', 'Memoir', 'Nature') keeps a row when its genre equals any value in the brackets. It means exactly the same as three = tests joined with OR, and it is shorter and harder to get wrong. The list goes in brackets, with commas between items. Text values each need their own single quotes; numbers do not need quotes.

IN also mixes safely with AND. With the long OR form you have to remember brackets; with IN, the list is already one condition, so the classic AND/OR bug from the last lesson cannot happen.

The order of the list does not matter, and duplicates in it are harmless. Matching is exact, as with =, so capital letters count.

SELECT title, genre
FROM loans
WHERE genre IN ('Fiction', 'Memoir', 'Nature');

How does NOT IN work?

NOT IN keeps rows whose value is none of the list. genre NOT IN ('Children', 'Travel') gives every loan for adult reading. It is the same as genre <> 'Children' AND genre <> 'Travel'.

Rows where the column itself is NULL are dropped by both IN and NOT IN, because an unknown value cannot be said to be in the list or out of it. The query below lists members by branch: Femi has no branch, so Femi appears in neither result. If you want those rows too, add OR branch IS NULL.

SELECT name, branch
FROM members
WHERE branch NOT IN ('Central', 'Hilltop');

Why can NOT IN return no rows at all?

Here is the gentle version of a famous trap. If the list after NOT IN contains a NULL, the condition can never be true, so the query returns nothing. branch NOT IN ('Central', NULL) asks "is the branch different from Central and different from an unknown value?" The database cannot say a value differs from an unknown one, so every row is dropped.

You will rarely type NULL into a list yourself. It happens when the list comes from another query, such as "members not in the list of members with overdue books", and one row of that inner result has a missing value. When you meet subqueries later, prefer NOT EXISTS for this kind of question, or make sure the inner query filters out NULLs. For now, remember the symptom: a NOT IN that suddenly returns zero rows.

-- Returns no rows: one value in the list is unknown
SELECT name, branch
FROM members
WHERE branch NOT IN ('Central', NULL);

How does BETWEEN test a range?

days_kept BETWEEN 10 AND 20 keeps rows where the value is at least 10 and at most 20. Both ends are included, so a loan kept exactly 10 days matches, and so does one kept exactly 20. It means the same as days_kept >= 10 AND days_kept <= 20.

The smaller value must come first. BETWEEN 20 AND 10 runs without an error. It can never be true, so you get no rows. That makes it an easy bug to miss: an empty result looks like "there were no matches".

NOT BETWEEN 10 AND 20 keeps values below 10 or above 20. Rows with NULL in the column are dropped by both forms, so the loans still out on loan appear in neither.

SELECT title, days_kept
FROM loans
WHERE days_kept BETWEEN 10 AND 20;

How do you use BETWEEN with dates?

Because dates are stored as YYYY-MM-DD text, BETWEEN works on them just as < and > do. loaned_on BETWEEN '2026-03-01' AND '2026-03-31' keeps every loan in March, including the first and the last day. Quote both dates.

Watch out when a column stores a date and a time, such as '2026-03-31 16:40'. That text is greater than '2026-03-31', so a loan taken out on the afternoon of 31 March falls outside the range. For columns with times, use loaned_on >= '2026-03-01' AND loaned_on < '2026-04-01' instead. The half-open form, from the start of one month up to but excluding the start of the next, also saves you remembering how many days a month has.

This table stores dates only, so BETWEEN is safe here. Open the Rows view on the task query: the WHERE step shows The Wild Robot on '2026-03-31' kept by the date test and dropped only because Children is not in the genre list.

SELECT title, loaned_on
FROM loans
WHERE loaned_on BETWEEN '2026-03-01' AND '2026-03-31';

Does BETWEEN work on text?

It does, using the same character order as ORDER BY. title BETWEEN 'A' AND 'M' keeps titles from A up to the single letter M. Matilda is not included, because 'Matilda' comes after 'M'. Text ranges are tricky for exactly this reason. For "titles starting with A to L", title >= 'A' AND title < 'M' is easier to read. Searching by the start of text gets its own lesson with LIKE.

Numbers and dates are where BETWEEN shines. Keep it for those, and reach for explicit comparisons whenever the column holds free text.

SELECT title
FROM loans
WHERE title BETWEEN 'A' AND 'M'
ORDER BY title;

What should you check in IN and BETWEEN code an AI wrote?

Check that each value in an IN list has its own quotes, and that the spelling and capitals match the data. Check that BETWEEN has the smaller value first, and ask whether both ends should really be included. On date columns, check whether times are stored, because then the last day of a BETWEEN range is cut short. Wherever you see NOT IN, ask whether the list could ever contain a missing value. Then run it and compare the row count with a quick count by eye in the Tables view.

Field notes

• IN ('a', 'b') is a shorter way to write several = tests joined with OR.
• NOT IN keeps rows whose value is none of the items in the list.
• BETWEEN includes both ends, and the smaller value must come first.
• Dates stored as YYYY-MM-DD text work with BETWEEN; quote both dates.

Easy to get wrong

One pair of quotes around both words makes a single value, "Fiction, Memoir", which no row has. Quote each value separately.

BETWEEN expects the smaller value first. Reversed, no value can satisfy both ends, so the result is empty with no error.

BETWEEN is inclusive, so the end date must be the last day you want, not the day after. March has 31 days.

A NULL in a NOT IN list makes the condition unknown for every row, so nothing is returned.

Questions people ask

How long can an IN list be?

SQLite handles lists of thousands of values. If your list is that long, the values probably belong in a table, and a join or subquery will be easier to maintain.

Is BETWEEN inclusive in every database?

Yes. The SQL standard defines it as including both ends, and SQLite, PostgreSQL, MySQL and SQL Server all follow that.

Is IN faster than several ORs?

They usually perform the same; the database treats them alike. Choose IN because it is easier to read and harder to get wrong.

Can the IN list come from another query?

Yes, WHERE member_id IN (SELECT id FROM members WHERE branch = 'Central') works. That is a subquery, covered later in the course.