SQL BETWEEN: Inclusive Boundaries With a Testable Example

The practice task

Select fictional assessment scores from 60 through 80, including both endpoints. Keep a below-range, above-range and missing score in the fixture so every boundary decision is visible.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE assessments(id INTEGER, score INTEGER);
INSERT INTO assessments VALUES (1,59),(2,60),(3,75),(4,80),(5,81),(6,NULL);

SELECT id, score FROM assessments
WHERE score BETWEEN 60 AND 80 ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Rows 2 and 4 confirm that the endpoints are included. Row 3 is inside the interval. The missing score does not pass this WHERE condition because the comparison does not establish that it is inside the range.

This integer example avoids date parsing differences between databases. A timestamp report needs an additional decision: a date label used as an upper bound may represent the start of that day, excluding later times on the same day. A half-open interval often makes a full-day reporting window clearer.

For repeated adjacent ranges, inclusive endpoints can overlap. If one band ends at 80 and the next starts at 80, the same score can qualify for both bands. Choose an explicit boundary policy instead of assuming that BETWEEN creates non-overlapping groups.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Check that the lower and upper endpoints are in the intended order.
• Test both endpoints, an outside value and a missing value.
• Do not assume text comparisons are numeric comparisons.