Question 12 of 20 Intermediate Concept
When should you use EXISTS rather than a JOIN to filter a cohort?
Short answer
Use EXISTS when the question is whether a matching row exists, not what it contains. EXISTS returns each patient at most once however many matches there are, while a JOIN returns one row per match and inflates counts unless you deduplicate.
Why
Suppose a cohort is defined as patients with at least one positive biomarker test. A JOIN from patients to tests returns a row for every positive test, so a patient tested positive four times appears four times, and COUNT(*) overstates the cohort. Adding DISTINCT hides the problem but makes the query harder to read and can hide a different join error.
EXISTS states the intent: keep the patient if the subquery returns at least one row. The PostgreSQL documentation describes exactly this behavior, and notes that the subquery can refer to columns of the outer query. The same pattern with NOT EXISTS finds patients with no positive test, and unlike NOT IN it is not broken by NULLs in the subquery.
A JOIN is the right tool when you need columns from the matched rows, such as the date of the first positive test, and then you control the multiplicity deliberately, for example by aggregating the tests to one row per patient before joining. Modern planners often execute EXISTS as a semi-join, so it is usually not slower. The choice is mainly about correctness and clarity.
A strong answer covers
- EXISTS tests for presence, returns each outer row once
- JOIN returns one row per match
- DISTINCT as a patch hides intent
- NOT EXISTS is safe with NULLs, NOT IN is not
- Aggregate before joining when columns are needed
Common mistakes
- Counting a cohort after a one-to-many join and reporting the number of tests as the number of patients
- Fixing an inflated count with SELECT DISTINCT without understanding which join caused it
The interviewer might ask next
How would you return each cohort patient with the date of their first positive test?