lesson 29 Dates, strings & subqueries · intermediate

Ask whether a row exists

Test whether matching rows exist without duplicating results.

the idea

EXISTS (subquery) is true when the subquery returns at least one row; NOT EXISTS is true when it returns none. Use it to ask whether related rows exist without multiplying the outer rows the way a join would.

Dataset: Healthcare

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Keep patients with a related row. Run this. EXISTS returns true when the subquery finds at least one inpatient encounter.

   step-1.sql Run

   sql›

   SELECT p.patient_id
   FROM patients p
   WHERE EXISTS (
     SELECT 1
     FROM encounters e
     WHERE e.patient_id = p.patient_id AND e.encounter_class = 'inpatient'
   )
   LIMIT 10;

2. 2

   See why join over-counts. Run this. Joining encounters repeats patients who have multiple inpatient stays.

   step-2.sql Run

   sql›

   SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT p.patient_id) AS patients
   FROM patients p
   JOIN encounters e ON e.patient_id = p.patient_id
   WHERE e.encounter_class = 'inpatient';

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT p.patient_id
   FROM patients p
   WHERE EXISTS (
     SELECT 1
     FROM encounters e
     WHERE e.patient_id = p.patient_id AND e.encounter_class = 'inpatient'
   );

common mistake

Joining patients to encounters to find who had an emergency visit returns a patient once per emergency visit unless you add DISTINCT.