Find rows whose value is outside a local range

By Batu · English technical notes

Also published in our primary archive.

To find measurements outside the inclusive range 0 through 10, write WHERE value < 0 OR value > 10. BETWEEN 0 AND 10 selects the opposite set: values inside the range, including zero and ten. SQLite expressions defines BETWEEN, while Python sqlite3 covers the standard-library interface used here.

The in-memory table holds five readings around both boundaries. The naive query prints 0, 5, and 10, which makes the error visible: it identifies valid measurements, not violations. The corrected predicate prints only -1 and 11. Both statements use ORDER BY value, because SQL result order is otherwise unspecified even when current insertion order happens to look stable.

This example assumes numeric, non-NULL measurements. A comparison with NULL produces SQL NULL, and WHERE retains only true expressions. If missing measurements are also invalid, add OR value IS NULL as an explicit policy choice. The assertions verify this fixture's boundaries; they do not validate units, tolerance rules, or ranges stored elsewhere. sqlite3 is included with Python, and the f-strings require Python 3.6+.

AI assistance disclosure: this was AI-assisted writing based on a deterministic local fixture.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE measurements (value INTEGER)")
seed_rows = [(-1,), (0,), (5,), (10,), (11,)]
connection.executemany("INSERT INTO measurements(value) VALUES (?)", seed_rows)

naive = connection.execute(
    "SELECT value FROM measurements WHERE value BETWEEN 0 AND 10 ORDER BY value"
).fetchall()
violations = connection.execute(
    "SELECT value FROM measurements WHERE value < 0 OR value > 10 ORDER BY value"
).fetchall()

assert naive == [(0,), (5,), (10,)]
assert violations == [(-1,), (11,)]
print("schema: measurements(value INTEGER)")
print(f"seed rows: {seed_rows}")
print(f"naive BETWEEN result: {[value for (value,) in naive]}")
print(f"corrected outside-range result: {[value for (value,) in violations]}")

schema: measurements(value INTEGER)
seed rows: [(-1,), (0,), (5,), (10,), (11,)]
naive BETWEEN result: [0, 5, 10]
corrected outside-range result: [-1, 11]