EXISTS, NOT EXISTS and nullable anti-joins

EXISTS tests whether a subquery produces any row; NOT EXISTS tests the opposite. The worked query uses a small staging set of observed company keys, including one unknown key. It asks which complete company identities have no matching observed key and correctly returns D.

The unknown staging row is deliberate external-data evidence, not a change to the roles table's non-null foreign key. Keeping that distinction explicit lets the lesson demonstrate nullable anti-joins without pretending the constrained source schema permits an impossible role record.

The correlated predicate compares a staging key with the outer company identity. A null key does not produce a true equality match. EXISTS only cares whether any matching row remains, so unrelated unknown keys do not remove the unmatched company D.

The selected expression inside EXISTS is conventionally one; its value is irrelevant to this existence question. Duplicating matching staging rows would not duplicate a company in the outer output. This is a useful alternative to a join when only eligibility evidence is needed.

The counterexample uses NOT IN against the nullable staging set. The unknown member can make the negative membership result unknown rather than true, so D disappears. This is a semantic trap, not a general statement that every NOT IN query is wrong.

When the subquery values are proved non-null and the intended missingness policy is clear, NOT IN can be valid. Do not cargo-cult a replacement. Trace the exact nullable input and use an explicit anti-match contract that remains correct for that input.

Find companies that have at least one active Ukrainian role. Use EXISTS over roles with the company relationship, status and vacancy-country predicates. Predict only A. You need not select or count every matching role to answer this parent-eligibility question.

Keep all three predicates inside the correlated subquery. The task requires one output row per company, so a raw role join would need additional reasoning about repeated companies. EXISTS expresses the requested yes-or-no relationship directly.

Step 1: Correlate the role with its company. Step 2: Require active status and UA inside EXISTS.

The solution returns A exactly once even though two active Ukrainian roles match. Archived role 7 does not change eligibility. If a new matching role were added in a disposable fixture, A should remain one parent row rather than appearing again.

The acceptance check should include a company with unrelated roles and a company with no roles. Both must be excluded from this positive existence result for different reasons. The original all-company table remains unchanged and available to other reports.

In interviews, explain the difference between scalar value, row multiplication and existence. For negative matching, draw the effect of a null subquery value instead of claiming that a keyword is universally safer or faster. Correctness assumptions should precede plan comparisons.

These patterns recur in permissions, skill evidence, missing applications and incomplete imports. The next lesson combines populations with set operators, where preserving repetitions versus removing them is another deliberate semantic choice.

Optional stretch — after the core task works: Verify both a non-null NOT IN case and the nullable staging case before comparing their meanings.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.