Um JOIN interno descarta as linhas sem par. Um LEFT JOIN mantém todas as linhas da tabela da esquerda e preenche com NULL as colunas da direita onde nada bateu, que é como você encontra o que falta:
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;
Três regras:
- Teste se a chave da tabela da direita é NULL. Uma coluna como
discharge_date também fica vazia em pares reais: pacientes que continuam num leito.
- Uma condição sobre a tabela da direita vai no ON. No WHERE ela descarta as linhas que a junção à esquerda preencheu com NULL, e a junção à esquerda vira, sem aviso, uma interna.
- Conte uma coluna da direita, como
COUNT(a.appointment_id), para obter 0 onde nada bateu. COUNT(*) conta a própria linha da esquerda e dá 1.
Todos os pacientes, com 0 para os que nunca faltaram a um agendamento.