Subqueries and Existence Tests
Some questions are not about a record's own fields but about whether related records exist. A subject with no detections this month. A check with no discount. A plate never seen at the main entrance.
Ordinary filters cannot express those, because the thing you are testing is somewhere else.
Existence checks
An existence check asks whether related records exist, and can be inverted to ask whether they do not. Add one from the advanced builder alongside your other conditions.
| Question | Shape |
|---|---|
| Subjects seen at least once this week | Existence check on detections |
| Subjects not seen this week | The same check, inverted |
| Checks that had a discount applied | Existence check on discounts |
| Checks with no discount | The same check, inverted |
The inverted form is the more valuable one. Absence is often what matters — the employee whose transactions never carry a manager approval, the enrolled subject who has stopped appearing.
Subqueries
A subquery is a query inside a query: it defines the related set, and the outer query filters against it.
Use one when the related records need conditions of their own. "Subjects with a detection at the north entrance after 22:00" needs both a location and a time on the detection, and a subquery is where those live.
Building one that behaves
The usual difficulty is being clear about which conditions belong where.
- Conditions about the thing you want back go in the outer query.
- Conditions about the related records you are testing for go in the subquery.
"Banned subjects seen at the north entrance yesterday" puts the tag in the outer query — it is a property of the subject — and the entrance and date in the subquery, because they are properties of the detection.
Getting this backwards typically returns everything or nothing, which is a useful diagnostic: if a subquery result looks absurd, check which side each condition landed on before rebuilding.
They cost more
A query that has to evaluate related records is heavier than one filtering plain fields. Two things keep it reasonable:
- Set a date range first. Bounding the period bounds the work.
- Filter the outer query as much as you can. Fewer records to test means less work per test.
A subquery over an unbounded period on an unfiltered set is the classic slow query.
Removing them
Remove existence check and Remove subquery take them out again. If a query has become hard to reason about, removing the subquery and re-running is a fast way to establish whether it is the source of a surprising result.
Simpler alternatives
Before building a subquery, check whether the module already offers what you want — tag statistics, zone visits and detection counts answer many "has this ever happened" questions directly, and are faster. See Detections and Zones.