Which Pumps Appear on Either List but Are Still Unfinished?

Sensor alerts flag P7 and P8. Technician requests flag P8 and P9. Botie needs one candidate list, then needs to remove pumps whose inspection is already complete.

This combines whole result rows. It does not match columns from two records into a wider row as a join does. We will decide when duplicate reports are meaningful before removing them.

Append compatible asset results, remove completed assets, and produce one ordered row per remaining pump.

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

Each side of a compound SELECT must return the same number of columns. Corresponding positions need compatible meanings and types. Selecting asset_id on one side and site_name on the other may run while producing a meaningless mixed column.

Choose the projected columns first. If you also select source, two rows for the same asset can remain different because their source labels differ.

Duplicate elimination compares the complete selected row.

UNION ALL appends every row from the second result to the first. It preserves repeated alerts rather than silently changing their count.

The resulting table still has no guaranteed presentation order without an outer ORDER BY. Input order or the order in which the SELECT clauses were written is not a replacement for a sorting requirement.

Preserve event IDs or source information when individual events matter.

CREATE TABLE alerts(asset_id TEXT);
CREATE TABLE requests(asset_id TEXT);
CREATE TABLE completed(asset_id TEXT);
INSERT INTO alerts VALUES ('P7'),('P7'),('P8');
INSERT INTO requests VALUES ('P8'),('P9');
INSERT INTO completed VALUES ('P8');

SELECT asset_id FROM alerts
UNION ALL
SELECT asset_id FROM requests
ORDER BY asset_id;

UNION removes duplicate complete output rows. With only asset_id projected, it returns one row per reported pump.

If each input is already unique, a pump can still occur in both inputs. Deduplicating each side separately is therefore insufficient for a distinct combined list.

The correct place to remove repetition depends on the result you are constructing.

CREATE TABLE alerts(asset_id TEXT);
CREATE TABLE requests(asset_id TEXT);
CREATE TABLE completed(asset_id TEXT);
INSERT INTO alerts VALUES ('P7'),('P7'),('P8');
INSERT INTO requests VALUES ('P8'),('P9');
INSERT INTO completed VALUES ('P8');

SELECT asset_id FROM alerts
UNION
SELECT asset_id FROM requests
ORDER BY asset_id;

INTERSECT keeps distinct rows present in both results. In this example it finds the assets supported by both an alert and a technician request.

The operation does not pair every alert with every request. It compares the selected result rows, so duplicate reports do not multiply the intersection.

Use a join instead when you need columns from the individual matching reports.

CREATE TABLE alerts(asset_id TEXT);
CREATE TABLE requests(asset_id TEXT);
CREATE TABLE completed(asset_id TEXT);
INSERT INTO alerts VALUES ('P7'),('P7'),('P8');
INSERT INTO requests VALUES ('P8'),('P9');
INSERT INTO completed VALUES ('P8');

SELECT asset_id FROM alerts
INTERSECT
SELECT asset_id FROM requests
ORDER BY asset_id;

EXCEPT keeps distinct rows from its left result that are absent from its right result. The order matters: candidates EXCEPT completed asks a different question from completed EXCEPT candidates.

Here we combine the report lists, then subtract completed asset IDs. When several operators appear together, make the intended stages clear. Engine precedence rules differ; a named subquery or CTE is often easier to review than relying on a reader to remember them.

The next lesson will give these intermediate result sets useful names.

SQLite’s compound duplicate comparisons treat NULL values in the same positions as equal for this purpose. That differs from using ordinary equality in a WHERE predicate. Text comparison also follows the chosen collation rules.

Decide whether a NULL asset ID belongs in an assignment report at all. Our exercise excludes NULL IDs explicitly because no pump can be assigned from an unknown identifier. It does not rely on an accidental NULL removal elsewhere.

A duplicate policy and a missing-identifier policy are separate decisions.

Run each projection, then the combined candidates, then the subtraction. Adding another P7 alert should not create another assignment. Adding P7 to completed should remove it.

The workshop exposes those same intermediate relations. It uses append followed by set difference, whose distinct result supplies the final one-row-per-asset policy.

Different valid SQL forms can express this result. The tests check membership and ordering rather than requiring one operator sequence.