Un JOIN interno descarta las filas sin pareja. Un LEFT JOIN conserva todas las filas de la tabla de la izquierda y rellena con NULL las columnas de la derecha donde nada coincidió, que es como encuentras lo 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;
Tres reglas:
- Comprueba si la clave de la tabla de la derecha es NULL. Una columna como
discharge_date también está vacía en parejas reales: pacientes que siguen en una cama.
- Una condición sobre la tabla de la derecha va en ON. En WHERE descarta las filas que la unión por la izquierda rellenó con NULL, y la unión por la izquierda se convierte sin avisar en una interna.
- Cuenta una columna de la derecha, como
COUNT(a.appointment_id), para obtener 0 donde nada coincidió. COUNT(*) cuenta la propia fila de la izquierda y da 1.
Todos los pacientes, con 0 para los que nunca faltaron a una cita.