SQL Self Join

A Self Join is a type of join that is used to combine and compare rows within the same table.

SQL Self Join Syntax

SELECT a.column1, b.column2, ...
FROM table_name a
JOIN table_name b ON a.common_field = b.common_field;

SELECT a.column1, b.column2, ...

FROM table_name a

JOIN table_name b ON a.common_field = b.common_field;

SQL Self Join Finding Patients with the Same Last Name Example

This query retrieves pairs of patients who share the same last name, which can be useful for identifying potential duplicates.

SELECT p1.first_name AS Patient1, p2.first_name AS Patient2
FROM Patients p1
JOIN Patients p2 ON p1.last_name = p2.last_name
WHERE p1.patient_id <> p2.patient_id;

SELECT p1.first_name AS Patient1, p2.first_name AS Patient2

FROM Patients p1

JOIN Patients p2 ON p1.last_name = p2.last_name

WHERE p1.patient_id <> p2.patient_id;

SQL Self Join Finding Appointments with Patients and Their Doctors Example

This query shows all patients along with their appointments and doctors, demonstrating a self-join concept by associating appointments back to the patients.

SELECT p.first_name AS Patient, a.appointment_date, d.first_name AS Doctor
FROM Patients p
JOIN Appointments a ON p.patient_id = a.patient_id
JOIN Doctors d ON a.doctor_id = d.doctor_id;

SELECT p.first_name AS Patient, a.appointment_date, d.first_name AS Doctor

FROM Patients p

JOIN Appointments a ON p.patient_id = a.patient_id

JOIN Doctors d ON a.doctor_id = d.doctor_id;

SQL Self Join Visual Diagram

SQL Self Join Labs