An inner JOIN drops rows with no match. A LEFT JOIN keeps every row of the left table and fills the right-hand columns with NULL where nothing matched, which is how you find what is missing:
SELECT p.mrn
FROM hd_patients p
LEFT JOIN hd_encounters e ON e.patient_id = p.patient_id
WHERE e.encounter_id IS NULL;
Three rules:
- Test the right-hand table's key for NULL. A column such as
discharge_date is also empty on real matches: patients still in a bed.
- A condition on the right-hand table belongs in ON. In WHERE it throws away the rows the left join filled with NULL, and the left join quietly becomes an inner join.
- Count a right-hand column, such as
COUNT(a.appointment_id), to get 0 where nothing matched. COUNT(*) counts the left row itself and gives 1.
Every patient, with 0 for those who never missed an appointment.