Practo hard FULL OUTER JOINCOALESCEGROUP BYCOUNT

Reconcile the doctors table with the appointments table so every doctor_id that shows up in either table gets exactly one row in the result — not just the ones that match. For each row, return the combined doctor_id, the doctor's name (or 'UNKNOWN DOCTOR' when no matching row exists in doctors), and appointment_count, the number of appointments tied to that doctor_id. A doctor with no appointments should show appointment_count = 0, not disappear from the result. Use FULL OUTER JOIN with COALESCE to build one unified doctor_id per group, then GROUP BY that combined key. Order by doctor_id ascending.

Practo · Doctor Registry Reconciliation — practice this real-world SQL scenario live in your browser.

📖 Story Practo · Doctor Registry Reconciliation

Tuesday afternoon at Practo's Data Platform team. A compliance audit flagged that the doctors table and the appointments table have drifted apart after a messy migration last quarter, and leadership wants a single reconciled view before the auditors show up on Friday. Some doctors — like Dr. Sanjay Gupta, who just onboarded — genuinely have zero appointments yet, and that's not a bug, it's just a brand-new profile. A handful of appointment rows reference a doctor_id that doesn't exist anywhere in the doctors table anymore, which is exactly the kind of orphaned reference the audit is worried about. And separately, a couple of walk-in appointment requests were logged with doctor_id left NULL on purpose, because the patient hadn't been assigned a doctor yet when the front desk created the booking — that NULL is legitimate business data, not corruption, and it needs to be told apart from the orphaned numeric IDs.

🎯 Your Mission

📋 Table Structure

🗂 doctors

doctor_id INTEGER 1

name TEXT Dr. Ananya Rao

specialty TEXT Cardiology

city TEXT Bangalore

🗂 appointments

appointment_id INTEGER 101

doctor_id INTEGER 1

patient_name TEXT Rahul K

appt_date TEXT 2026-09-01

status TEXT completed

⚡ Step-by-Step Walkthrough

Look at doctors next to their appointments with a LEFT JOIN

query.sql

SELECT d.doctor_id, d.name, a.appointment_id, a.doctor_id AS appt_doctor_id, a.status
FROM doctors d
LEFT JOIN appointments a ON a.doctor_id = d.doctor_id
ORDER BY d.doctor_id, a.appointment_id;

💡 Explanation

LEFT JOIN keeps every doctor row even when nothing in appointments matches it — Dr. Sanjay Gupta (doctor_id 6) shows up with NULL for appointment_id and status, because he genuinely has zero rows in appointments, not because anything went wrong.
Every other doctor shows one row per appointment they're linked to — Dr. Ananya Rao (doctor_id 1) and Dr. Vikram Shah (doctor_id 2) each have two, everyone else has one.
This view is anchored on doctors, so it can only ever show appointments whose doctor_id matches a real row in doctors — the four appointment rows with doctor_id 7, 9, or NULL simply never appear here, no matter how the JOIN condition is written, because a LEFT JOIN starting from doctors has no way to surface a row that doctors doesn't have.
That's the gap the rest of this question exists to close — a doctor-centric view can prove which doctors are quiet, but it's structurally blind to appointments pointing at doctor_ids that don't exist.

The trap: INNER JOIN looks like a full reconciliation but isn't

query.sql

SELECT d.doctor_id, d.name, COUNT(a.appointment_id) AS appointment_count
FROM doctors d
JOIN appointments a ON a.doctor_id = d.doctor_id
GROUP BY d.doctor_id, d.name
ORDER BY d.doctor_id;

💡 Explanation

This reads like a clean reconciliation — group appointments by doctor, count them — and it runs without error, which is exactly what makes it dangerous.
Dr. Sanjay Gupta (doctor_id 6) is gone from the result entirely, not shown with appointment_count = 0 — an INNER JOIN only keeps rows where both sides match, so a doctor with zero appointments has nothing to match against and is silently dropped.
The four problem appointments — two pointing at doctor_id 7 and 9, which don't exist in doctors, and two logged with doctor_id NULL — never make it into this result either, for the same reason: there's no doctors row for them to join to.
An auditor reading this output would conclude every doctor has at least one appointment and every appointment belongs to a real doctor — both conclusions are false, and the query gave no error or warning to say so.

Fix it: FULL OUTER JOIN with COALESCE keeps both sides

query.sql

SELECT COALESCE(d.doctor_id, a.doctor_id) AS doctor_id,
       COALESCE(d.name, 'UNKNOWN DOCTOR') AS doctor_name,
       COUNT(a.appointment_id) AS appointment_count
FROM doctors d
FULL OUTER JOIN appointments a ON a.doctor_id = d.doctor_id
GROUP BY COALESCE(d.doctor_id, a.doctor_id), COALESCE(d.name, 'UNKNOWN DOCTOR')
ORDER BY doctor_id;

💡 Explanation

FULL OUTER JOIN keeps a row for every doctor with no match in appointments AND every appointment with no match in doctors, at the same time — it's a LEFT JOIN and a RIGHT JOIN combined, so Dr. Sanjay Gupta and the four problem appointments all survive in the same result.
For an unmatched doctor, a.doctor_id is NULL; for an unmatched appointment, d.doctor_id is NULL — COALESCE(d.doctor_id, a.doctor_id) picks whichever side actually has a value, so every row still gets one real doctor_id to group and sort by, instead of a mix of two half-empty columns.
The two walk-in appointments with a genuinely NULL doctor_id in the source data collapse into a single 'UNKNOWN DOCTOR' row with appointment_count = 2 — GROUP BY treats all NULLs in a column as one group — while doctor_id 7 and doctor_id 9 stay as two separate 'UNKNOWN DOCTOR' rows with appointment_count = 1 each, because 7 and 9 are actual distinct values, not NULL, even though neither matches a real doctor.
Dr. Sanjay Gupta now appears correctly with appointment_count = 0 instead of vanishing, because COUNT(a.appointment_id) counts only non-NULL appointment_id values, and an unmatched doctor's a.appointment_id is NULL — nine rows come back in total: six real doctors, plus three groups for doctor_ids that never matched anyone.

▶ Practice this live ← Browse all questions