内部JOINは、一致のない行を落とします。LEFT JOINは左のテーブルのすべての行を残し、何も一致しないところでは右側の列をNULLで埋めます。これで欠けているものを見つけられます:
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;
3つの規則があります:
- 右側のテーブルのキーがNULLかを確認する。
discharge_dateのような列は、本当に一致したものでも空のことがあります:まだベッドにいる患者です。
- 右側のテーブルの条件はONに入れる。WHEREに入れると、左結合がNULLで埋めた行が捨てられ、左結合が知らないうちに内部結合になります。
- 何も一致しないところで0を得るには、
COUNT(a.appointment_id)のように右側の列を数える。COUNT(*)は左の行そのものを数えるので、1になります。