WHERE and Filtering

The Idea

WHERE runs a yes-or-no test on every candidate row and keeps only the passes. Combine tests with AND and OR, and remember the odd one out: NULL means unknown, so mites = NULL is never true. You must ask IS NULL.

Real-World Example

An apiary inspector with sixty hives does not open all sixty. The record cards are pulled for hives whose mite count is over the treatment threshold and whose queen was not sighted last visit — a short list, decided before anyone walks into the field.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE hive (name TEXT, mites INTEGER, queen_seen INTEGER);
  INSERT INTO hive VALUES ('Willow', 12, 1), ('Chalk', 47, 1),
                          ('Beacon', 3, 0), ('Long Mead', 51, 0);
""")
rows = db.execute("""
  SELECT name, mites FROM hive
  WHERE mites > 40 AND queen_seen = 0
""").fetchall()
print(rows)
# [('Long Mead', 51)]

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE hive (name TEXT, mites INTEGER);
INSERT INTO hive VALUES ('Willow', 12), ('Chalk', NULL);
""")
# Which hives have no mite count recorded at all?
print(db.execute("SELECT name FROM hive WHERE mites ___").fetchall())
# -> [('Chalk',)]