SQL NOT EXISTS: Check Required Evidence for Each Record

The practice task

Find fictional practice submissions missing at least one required artifact. Every participant must supply both a brief and a sample. This is a completeness exercise, not a real applicant-selection rule.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE participants(id INTEGER, name TEXT);
CREATE TABLE required(kind TEXT);
CREATE TABLE submitted(person_id INTEGER, kind TEXT);
INSERT INTO participants VALUES (1,'Ari'),(2,'Bo'),(3,'Cy');
INSERT INTO required VALUES ('brief'),('sample');
INSERT INTO submitted VALUES (1,'brief'),(1,'sample'),(2,'brief'),(3,'sample');

SELECT p.name FROM participants AS p
WHERE EXISTS (
 SELECT 1 FROM required AS r
 WHERE NOT EXISTS (
  SELECT 1 FROM submitted AS s
  WHERE s.person_id = p.id AND s.kind = r.kind
 )
) ORDER BY p.name;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

For each participant, the middle query looks for a required kind with no matching submitted row. Ari has both required kinds and is excluded. Bo lacks a sample; Cy lacks a brief. Both therefore appear in the incomplete list.

The innermost condition uses two connections: the participant ID and the required kind. Omitting either changes the question. A sample from any participant cannot satisfy Bo, and an unrelated artifact from Bo cannot satisfy the sample requirement.

EXISTS is about whether a qualifying row exists, not the value selected by SELECT 1. Multiple matching submission rows do not change the existence answer. They may still be a data-quality issue worth reporting separately, but they do not require a COUNT for this completeness check.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Read each correlation condition aloud against the task.
• Test a complete participant and two different missing-artifact cases.
• Do not mistake an artifact supplied by another participant for a match.