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;
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;
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;
