BETWEEN and IN
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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;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.
type IN ('fire', 'water')level BETWEEN 15 AND 19type NOT IN ('plant')power NOT BETWEEN 72 AND 82Exercise 7 — Shorter with IN
Which query is equivalent to this one?
SELECT name
FROM codemon
WHERE type = 'fire' OR type = 'plant' OR type = 'water';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.
-- Write your query below