LambdaLabTM
Databases & SQL · Class 12 · Querying with SELECT
MySQLSELECT⏱️ 8 min read

BETWEEN & IN

Two shorthands. Neither lets you ask anything you could not already ask with and and or — they just say it in a way that is shorter to write and much easier to read.

1BETWEEN — a range

MySQL command line client
mysql> select id, name, percent from students where percent between 70 and 90;
+------+--------+---------+
| id | name | percent |
+------+--------+---------+
| 2035 | Ravi | 83.75 |
| 2044 | Karma | 78.5 |
| 2199 | Diana | 88.25 |
| 3301 | Farhan | 72.8 |
+------+--------+---------+
4 rows in set (0.00 sec)

That is exactly the same question as:

where percent >= 70 and percent <= 90
Both ends are included
This is the one thing to remember about between. A student scoring exactly 70 is in, and so is one scoring exactly 90. Read it as “from 70 to 90 inclusive”, not “strictly between”. Exam questions are written to catch you on precisely this.

The smaller value must come first. between 90 and 70 is valid SQL but finds nothing, because no number is both at least 90 and at most 70.

2BETWEEN works on dates too

Anything that can be compared can be given a range, so this is the neat way to ask for a period of time:

where dob between '2009-01-01' and '2009-12-31'

— everyone born in 2009, both ends of the year included. It is shorter and clearer than two conditions joined with and.

3NOT BETWEEN

MySQL command line client
mysql> select id, name, percent from students where percent not between 70 and 90;
+------+--------+---------+
| id | name | percent |
+------+--------+---------+
| 1099 | Veena | 90.5 |
| 1187 | Anjali | 91 |
| 1250 | Sohan | 66 |
| 1402 | Lhamu | 95.2 |
| 3033 | Tom | 64.25 |
| 3612 | Nima | 59.4 |
+------+--------+---------+
6 rows in set (0.00 sec)

Everyone outside the range, at either end. Four rows in the range plus six outside makes ten — not twelve, because Bhim and Pema have NULL percentages and fall out of both, exactly as they did with <> in the last lesson.

4IN — a list of values

MySQL command line client
mysql> select id, name, city from students where city in ('Gangtok','Namchi');
+------+--------+---------+
| id | name | city |
+------+--------+---------+
| 1187 | Anjali | Gangtok |
| 1402 | Lhamu | Namchi |
| 2035 | Ravi | Gangtok |
| 3120 | Pema | Namchi |
+------+--------+---------+
4 rows in set (0.00 sec)

The same question written the long way:

where city = 'Gangtok' or city = 'Namchi'

With two values the saving is small. With six it is the difference between a readable query and a mess — and the column name is written once instead of six times, so there is one place to get it wrong instead of six.

IN is a list, not a range
in (70, 90) means exactly 70 or exactly 90 — two values. between 70 and 90 means everything from 70 to 90. Mixing these up is a common slip; the brackets and commas of in are the reminder that it is a list.

5NOT IN

MySQL command line client
mysql> select id, name, city from students where city not in ('Gangtok','Namchi');
+------+--------+----------+
| id | name | city |
+------+--------+----------+
| 1099 | Veena | Singtam |
| 2044 | Karma | Singtam |
| 2199 | Diana | Kolkata |
| 2500 | Bhim | Singtam |
| 3033 | Tom | Kolkata |
| 3301 | Farhan | Siliguri |
+------+--------+----------+
6 rows in set (0.00 sec)

Six students from anywhere else — and once again the two with a NULL city are in neither list.

6Recap

col between a and b

A range, with BOTH ends included. Same as >= a and <= b.

col in (v1, v2, …)

A list of exact values. Same as = v1 or = v2 or …

not between / not in

The opposite in each case.

NULLs are excluded

As always — a row with NULL matches neither the condition nor its negation.

Quick Check

A student scores exactly 70. Is the row returned by where percent between 70 and 90?

Quick Check

Which is equivalent to where city = 'Gangtok' or city = 'Namchi'?

Quick Check

What does where grade in (9, 12) return?

← Previous
🔀 AND, OR & NOT
Next →
IS NULL & IS NOT NULL 🕳️