Which Pumps Still Have No Completed Visit?

Ask whether a related row exists, keep the outer row’s identity, and avoid treating an unknown key as evidence that every candidate is present.

13 sections · 4 questions · 1 coding challenge

1 / 13

The arrow keys move between sections too.

Finding Absence Without Manufacturing More Rows

BOTIE

Relational queries often need to express that a related fact is present or absent. SQL’s EXISTS expression answers that question from the presence of rows, while NOT EXISTS reverses it. This keeps the result at the outer table’s grain instead of producing one row for every matching pair.

The distinction is useful for a maintenance log that can contain several visits per pump. We care whether any completed visit exists, not how many matching copies a join could produce.

Sources for this history

A Missing Match Is a Useful Answer

The maintenance desk has an asset list and a visit log. Botie needs the pumps that still lack a completed visit. Joining the tables gives us matching visits, but our question concerns the pumps left without one.

We will ask whether a matching completed row exists for each asset. An incomplete log row with no asset ID should not make every unvisited pump disappear.

The records are invented maintenance examples. The workshop exposes record operations; the SQL worksheet executes a real query in an isolated SQLite database.

Step 1 of 1

Follow one asset at a time

Only closed visits count. Two closed visits for P7 answer the same existence question.

A visit whose asset_id is NULL does not match any named pump.
Asset	Matching closed visits	Keep with NOT EXISTS?
P7	Two	No
P8	None; its visit is open	Yes
P9	None	Yes

P7
Matching closed visits
Two
Keep with NOT EXISTS?
No
P8
Matching closed visits
None; its visit is open
Keep with NOT EXISTS?
Yes
P9
Matching closed visits
None
Keep with NOT EXISTS?
Yes

A visit whose asset_id is NULL does not match any named pump.

Absence is tested per outer asset

The existence branch preserves one outer asset row and treats NULL keys as nonmatches.

Keep assets without a completed match

Filter completed visits first, then test their existence for each asset.

BOTIE

The visit log contains open work, repeated completed work and an unattributed record.

Choose an example

Registered asset rows

asset_id

Visit log

asset_id

status

asset_id

status

asset_id

status

Asset IDs without completed visits

[
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]

Follow the operations

1. Registered asset rows

[
  {
    "asset_id": "P7"
  },
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]

2. Visit log

[
  {
    "asset_id": "P7",
    "status": "closed"
  },
  {
    "asset_id": "P7",
    "status": "closed"
  },
  {
    "asset_id": "P8",
    "status": "open"
  }
]

3. Set a text value
```
closed
```

4. Keep rows passing a comparison

[
  {
    "asset_id": "P7",
    "status": "closed"
  },
  {
    "asset_id": "P7",
    "status": "closed"
  }
]

5. Keep rows by existence of a match

[
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]

6. Choose table columns

[
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]

7. Order rows by named fields

[
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]

8. Result

[
  {
    "asset_id": "P8"
  },
  {
    "asset_id": "P9"
  }
]

The operation models an equality-based anti-join over the supplied records. It is not a query planner.

1. Decide what counts as a qualifying match

A visit qualifies only when its asset_id matches the current asset and its status is closed. Put both conditions inside the existence test. An open visit does not satisfy the request simply because it names the same pump.

The output should contain one row per asset. The number of visits associated with a pump must not change that grain.

Begin with the relationship and the qualifying condition before choosing the SQL form.

2. Observe EXISTS on a small result

EXISTS returns true when its subquery produces any row. The selected value is usually unimportant, so SELECT 1 communicates that we only need a match. A row containing NULL still exists.

An optimiser may stop once it can determine the result. Do not write functions with side effects inside a subquery and rely on a particular number of evaluations.

The question is about row presence, not whether a selected cell is non-NULL.

A NULL cell still occupies a row

This expression has no maintenance fixture because the row-presence distinction can be demonstrated directly.

Database setup for this example

SQL · SQLite

SELECT EXISTS(SELECT NULL), EXISTS(SELECT 1 WHERE 0);

Query result

EXISTS(SELECT NULL)	EXISTS(SELECT 1 WHERE 0)
1	0

The first subquery has one row and the second has none. SQLite represents these truth values as 1 and 0.

3. Connect the inner match to the outer asset

The outer alias a identifies the pump currently under consideration. The inner alias v names candidate visit rows. The predicate v.asset_id = a.asset_id ties the two together.

NOT EXISTS keeps an asset when the qualified inner result is empty. Ordering the final asset IDs makes the report stable.

A condition using only inner-table columns could accidentally ask the same global question for every asset.

Find assets with no closed visit

The unattributed closed row has a NULL asset_id and cannot satisfy equality with P8 or P9.

assets

asset_id
P7
P8
P9

visits

visit_id	asset_id	status
1	P7	closed
2	P7	closed
3	P8	open
4	NULL	closed

Database setup for this example

SQL · SQLite

CREATE TABLE assets(asset_id TEXT PRIMARY KEY NOT NULL);
CREATE TABLE visits(visit_id INTEGER PRIMARY KEY, asset_id TEXT REFERENCES assets(asset_id), status TEXT NOT NULL);
INSERT INTO assets VALUES ('P7'),('P8'),('P9');
INSERT INTO visits VALUES (1,'P7','closed'),(2,'P7','closed'),(3,'P8','open'),(4,NULL,'closed');

SQL · SQLite

SELECT a.asset_id
FROM assets AS a
WHERE NOT EXISTS (
  SELECT 1 FROM visits AS v
  WHERE v.asset_id = a.asset_id AND v.status = 'closed'
)
ORDER BY a.asset_id;

Query result

asset_id
P8
P9

Both pumps lack a qualifying match. The duplicate P7 visits do not duplicate any outer row.

4. Work through the NOT IN trap

A NOT IN subquery containing NULL can make an otherwise unmatched comparison unknown. The database cannot establish that the named pump differs from an unknown value. WHERE retains true rows, so those unknown results disappear.

Filtering NULL values out of a suitable NOT IN subquery can resolve that particular issue. NOT EXISTS expresses this lesson’s correlated no-match question directly and makes the matching rule easy to inspect.

The two forms are not automatically interchangeable when nullable keys are involved.

See why the tempting alternative loses rows

The closed-visit list contains P7 and NULL.

assets

asset_id
P7
P8
P9

visits

visit_id	asset_id	status
1	P7	closed
2	P7	closed
3	P8	open
4	NULL	closed

Database setup for this example

SQL · SQLite

CREATE TABLE assets(asset_id TEXT PRIMARY KEY NOT NULL);
CREATE TABLE visits(visit_id INTEGER PRIMARY KEY, asset_id TEXT REFERENCES assets(asset_id), status TEXT NOT NULL);
INSERT INTO assets VALUES ('P7'),('P8'),('P9');
INSERT INTO visits VALUES (1,'P7','closed'),(2,'P7','closed'),(3,'P8','open'),(4,NULL,'closed');

SQL · SQLite

SELECT asset_id FROM assets
WHERE asset_id NOT IN (SELECT asset_id FROM visits WHERE status='closed')
ORDER BY asset_id;

Query result

asset_id

No rows.

The empty result is caused by unknown membership comparisons. It does not mean that P8 and P9 were inspected.

5. Keep the anti-join’s conditions in the right place

A left join followed by a missing-right-key check can also express an anti-join. Its match conditions must include the qualifying status, and the field tested for missingness should be guaranteed non-NULL on a real match.

For this schema, visit_id is a primary key. Testing a nullable notes field instead would confuse “a matching visit with no notes” with “no matching visit.” EXISTS avoids needing that sentinel column.

Pick the form whose grain and missing-row interpretation you can explain. Check the execution plan when performance becomes material.

6. Test presence, absence and duplicate matches

Add another completed visit for P7: the awaiting list should stay the same. Complete P8’s visit: only P9 remains. Remove every visit: every asset should appear.

These changes test the meaning of the query more directly than requiring a particular spelling. The workshop uses the same existence operation over records, with a separate filter for closed visits.

A correct no-match query should still work when there are no matching records at all.

Which Pumps Still Have No Completed Visit?

Finding Absence Without Manufacturing More Rows

A Missing Match Is a Useful Answer

Follow one asset at a time

Absence is tested per outer asset

Keep assets without a completed match

Asset IDs without completed visits

1. Decide what counts as a qualifying match

2. Observe EXISTS on a small result

A NULL cell still occupies a row

3. Connect the inner match to the outer asset

Find assets with no closed visit

Questions on Parts 1 to 3

4. Work through the NOT IN trap

See why the tempting alternative loses rows

5. Keep the anti-join’s conditions in the right place

6. Test presence, absence and duplicate matches

Questions on Parts 1 to 6

Build with Botie

Practice. Report pumps awaiting a completed visit