BETWEEN and IN

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — A range with BETWEEN

To keep the rows whose value falls inside a range, you could combine two comparisons with AND. SQL has a shorter way: BETWEEN.

These two queries return exactly the same Codémons:

SELECT name, level FROM codemon WHERE level >= 15 AND level <= 19;SELECT name, level FROM codemon WHERE level BETWEEN 15 AND 19;

BETWEEN is inclusive: both bounds are part of the range. Aquachu (level 15) and Florachu (level 19) are kept.

Lower bound first

Always write the smaller value first. level BETWEEN 19 AND 15 is valid SQL but returns no rows at all, because no number is both ≥ 19 and ≤ 15.

Exercise 2 — Outside the range: NOT BETWEEN

Add NOT to keep the rows that are outside the range instead:

SELECT name, power FROM codemon WHERE power NOT BETWEEN 72 AND 82;

Only Florachu (70) and Firochu (85) are outside 72–82.

BETWEEN also works with text (alphabetical order) and, as you'll see in the Dates class, with dates.

SELECT name FROM codemon WHERE name BETWEEN 'A' AND 'G';

Why no name starting with G?

With text, BETWEEN 'A' AND 'G' keeps names alphabetically between the one-letter string 'A' and the one-letter string 'G'. A name like 'Grass' comes *after* 'G', so it would be excluded. For text, the LIKE operator from the next chapter is usually a better tool.

Exercise 3 — Who is in range?

Which Codémons does this query return?

SELECT name FROM codemon WHERE level BETWEEN 18 AND 22;
Options
• Florachu, Pyrochu and Plantachu
• Florachu only — the bounds are excluded
• Pyrochu only
• Every Codémon

Hint : BETWEEN includes both bounds — check whether the Codémons at level 18 and at level 22 are kept.

Exercise 4 — Mid-range power

Complete the query to find the Codémons whose power is between 70 and 80, inclusive.

Hint : The range operator is BETWEEN, and its two bounds are separated by AND.

Exercise 5 — A list of values with IN

When a value must match one of several possibilities, chaining OR conditions gets long quickly. IN takes a list instead:

SELECT name, type FROM codemon WHERE type = 'fire' OR type = 'water';SELECT name, type FROM codemon WHERE type IN ('fire', 'water');

The list goes between parentheses, values separated by commas. It works with numbers too:

SELECT name, level FROM codemon WHERE level IN (13, 15, 22);

And NOT IN keeps everything that is not in the list:

SELECT name, type FROM codemon WHERE type NOT IN ('plant');

IN and subqueries

Later in the course you'll see that the list inside IN can itself be produced by another query. That's one of the most common uses of IN in real projects.

Exercise 6 — Say it in words

Match each condition with its meaning.

Items to match
• type IN ('fire', 'water')
• level BETWEEN 15 AND 19
• type NOT IN ('plant')
• power NOT BETWEEN 72 AND 82
Definitions
• The type is fire or water
• The level goes from 15 to 19, both included
• Every type except plant
• The power is below 72 or above 82

Exercise 7 — Shorter with IN

Which query is equivalent to this one?

SELECT name FROM codemon WHERE type = 'fire' OR type = 'plant' OR type = 'water';
Options
• SELECT name FROM codemon WHERE type IN ('fire', 'plant', 'water');
• SELECT name FROM codemon WHERE type = ('fire', 'plant', 'water');
• SELECT name FROM codemon WHERE type IN 'fire', 'plant', 'water';
• SELECT name FROM codemon WHERE type AND ('fire', 'plant', 'water');

Hint : IN needs its list between parentheses, with the values separated by commas.

Exercise 8 — Water or plant

Complete the query to keep only the Codémons whose type is water or plant, using a list.

Hint : The operator is IN, and the list of values is wrapped in parentheses.

Exercise 9 — The scouting list

A scout is looking for mid-level water or fire Codémons.

Write a query that returns the name, level and power of the Codémons whose level is between 15 and 20 (inclusive) and whose type is water or fire. Use BETWEEN and IN.

Starter code
-- Write your query below
Back to the course