SQL Joins: Healthcare Data Analysis

In this scenario, we will create SQL queries to analyze healthcare data involving patients, doctors, appointments, and treatments. We will use various types of SQL joins to derive insights from the data, such as patient treatment history, doctor performance metrics, and appointment schedules.

Table Structures and Example Data

1. patients

2. doctors

3. appointments

4. treatments

SQL Joins: Advanced Examples with Outputs

1. Inner Join: Patient Treatment History

This query retrieves the complete treatment history for each patient, including details of the doctor who administered the treatment.

SELECT
    p.patient_id,
    p.name AS patient_name,
    d.name AS doctor_name,
    a.appointment_date,
    t.treatment_type,
    t.cost
FROM
    patients p
JOIN
    appointments a ON p.patient_id = a.patient_id
JOIN
    doctors d ON a.doctor_id = d.doctor_id
JOIN
    treatments t ON a.appointment_id = t.appointment_id
ORDER BY
    p.patient_id, a.appointment_date;

Output:

2. Left Join: Upcoming Appointments

This query lists all patients and their upcoming appointments, if any. Patients without upcoming appointments will also be included in the results.

SELECT
    p.patient_id,
    p.name AS patient_name,
    a.appointment_date,
    d.name AS doctor_name,
    a.status
FROM
    patients p
LEFT JOIN
    appointments a ON p.patient_id = a.patient_id AND a.status = 'Scheduled'
LEFT JOIN
    doctors d ON a.doctor_id = d.doctor_id
WHERE
    a.appointment_date >= CURRENT_DATE
ORDER BY
    p.patient_id, a.appointment_date;

Output:

3. Right Join: Doctor Performance Metrics

This query provides performance metrics for each doctor, including the number of appointments they have handled and the total cost of treatments they have administered. Doctors with no appointments will also be included.

SELECT
    d.doctor_id,
    d.name AS doctor_name,
    d.specialty,
    COUNT(a.appointment_id) AS total_appointments,
    SUM(t.cost) AS total_treatment_cost
FROM
    doctors d
RIGHT JOIN
    appointments a ON d.doctor_id = a.doctor_id
LEFT JOIN
    treatments t ON a.appointment_id = t.appointment_id
GROUP BY
    d.doctor_id, d.name, d.specialty
ORDER BY
    total_appointments DESC;
SELECT
    d.doctor_id,
    d.name AS doctor_name,
    d.specialty,
    COUNT(a.appointment_id) AS total_appointments,
    SUM(t.cost) AS total_treatment_cost
FROM
    doctors d
RIGHT JOIN
    appointments a ON d.doctor_id = a.doctor_id
LEFT JOIN
    treatments t ON a.appointment_id = t.appointment_id
GROUP BY
    d.doctor_id, d.name, d.specialty
ORDER BY
    total_appointments DESC;

Output:

4. Full Outer Join: Appointment and Treatment Audit

This query creates a comprehensive audit report by listing all appointments and their corresponding treatments, if any. Both appointments without treatments and treatments without appointments will be included.

SELECT
    a.appointment_id,
    a.appointment_date,
    a.status,
    p.name AS patient_name,
    d.name AS doctor_name,
    t.treatment_type,
    t.cost
FROM
    appointments a
FULL OUTER JOIN
    treatments t ON a.appointment_id = t.appointment_id
LEFT JOIN
    patients p ON a.patient_id = p.patient_id
LEFT JOIN
    doctors d ON a.doctor_id = d.doctor_id
ORDER BY
    a.appointment_date;

Output:

5. Self Join: Patient Referral Network

This query identifies patients who have been referred by other patients. The referred_by field in the patients table indicates which patient referred them.

SELECT
    p1.patient_id AS patient_id,
    p1.name AS patient_name,
    p2.patient_id AS referred_by_id,
    p2.name AS referred_by_name
FROM
    patients p1
JOIN
    patients p2 ON p1.referred_by = p2.patient_id
ORDER BY
    p1.patient_id;

Output: