Was the Visit Instant, or Is Its Duration Missing?

One visit has a recorded duration of zero. Another has no recorded duration at all. Botie cannot treat those as the same fact. Zero tells us what was entered; the missing cell tells us that this field supplies no duration.

The distinction affects filters, averages and whether a record needs follow-up. SQL uses NULL for missing values, and a comparison involving NULL often has an unknown result rather than a simple true or false.

Use the missing-value test, then keep visit and asset identities for follow-up.

[
  {
    "inspection_id": 102,
    "asset_id": "P1"
  }
]

NULL can represent a value that is unknown, unavailable or not applicable. Those reasons are not automatically distinguishable from the marker alone. If the maintenance process needs to distinguish not measured from sensor failed, store a separate status.

The duration of zero is still a stored number. It may be valid or it may need quality review, but replacing it with NULL would change the recorded fact.

Missingness needs a domain explanation as well as a database representation.

A comparison such as minutes = NULL asks whether a value equals an unknown value. It does not become true for a missing cell. Use IS NULL to test whether the marker is present, or IS NOT NULL for its absence.

Most ordinary comparisons involving NULL evaluate to unknown. WHERE retains only true results. Thus minutes <> 10 excludes both known tens and unknown durations, even though an unknown duration has not been shown to equal ten.

Negating an unknown comparison does not turn it into a known true statement.

If one condition is false, an AND expression cannot become true regardless of the other condition. If one condition is true, an OR expression is true regardless of the other. Other combinations can remain unknown.

You do not need to memorise every row of a truth table before using the idea. Ask whether the known part is already sufficient to determine the combined condition. Use an explicit missingness clause when missing records should be retained.

A condition that keeps known failures may need a separate branch to keep unknown measurements.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT inspection_id, minutes FROM inspections
WHERE minutes IS NULL OR minutes = 0
ORDER BY inspection_id;

DISTINCT can collapse repeated NULL-containing result rows. GROUP BY collects NULL keys into one group. Those behaviours do not mean that NULL = NULL becomes true in a WHERE predicate.

Keep the operation in view when reasoning about a result. The workshop’s IS NULL filter is a missingness test. Later grouping and aggregation lessons will make their own treatment of missing values explicit.

A rule learned for one SQL operation is not automatically the rule for every other operation.

COALESCE can provide the first non-null value from a list of alternatives. A user-facing note may reasonably fall back to awaiting entry. Replacing an unknown duration with zero before calculating an average makes a much stronger claim: it treats the unknown visit as a zero-minute visit.

That may distort both a report and a machine-learning feature. Decide whether the calculation needs known-only observations, an explicit imputation policy, or a missingness indicator.

A convenient display fallback and a justified numeric imputation are different decisions.

A dataset filtered with temperature > 75 silently loses rows whose temperature is missing. A model trained on the remaining rows may see a different population if missing readings cluster at particular sites or devices.

Count or inspect missing values alongside the main report. Preserve the original measurement and document any transformation so a later analyst can understand which facts were observed and which were supplied by policy.

SQL can express the inclusion rule precisely; it cannot decide whether that rule is appropriate for the analysis.

The changed workshop includes a zero-minute visit and a missing visit with different identifiers. Select only the missing one. A construction that compares against zero, or replaces NULL with zero and then compares, cannot preserve this distinction.

When the missing field is later filled in, the row should leave the follow-up report automatically.

The report is a query over the current state, not a memorised list of problem IDs.