{"id":241,"date":"2024-08-23T02:04:53","date_gmt":"2024-08-23T02:04:53","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=241"},"modified":"2025-05-13T13:20:55","modified_gmt":"2025-05-13T13:20:55","slug":"sql-self-join","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/sql-self-join\/","title":{"rendered":"SQL Self Join"},"content":{"rendered":"\n

A Self Join is a type of join that is used to combine and compare rows within the same table.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL Self Join Syntax<\/h2>\n\n\n\n
SELECT a.column1, b.column2, ...\nFROM table_name a\nJOIN table_name b ON a.common_field = b.common_field;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Self Join Finding Patients with the Same Last Name Example<\/h2>\n\n\n\n
This query retrieves pairs of patients who share the same last name, which can be useful for identifying potential duplicates.<\/p>\n\n\n\n
SELECT p1.first_name AS Patient1, p2.first_name AS Patient2\nFROM Patients p1\nJOIN Patients p2 ON p1.last_name = p2.last_name\nWHERE p1.patient_id <> p2.patient_id;<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Try It Out!<\/a><\/div>\n<\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Self Join Finding Appointments with Patients and Their Doctors Example<\/h2>\n\n\n\n
This query shows all patients along with their appointments and doctors, demonstrating a self-join concept by associating appointments back to the patients.<\/p>\n\n\n\n
SELECT p.first_name AS Patient, a.appointment_date, d.first_name AS Doctor\nFROM Patients p\nJOIN Appointments a ON p.patient_id = a.patient_id\nJOIN Doctors d ON a.doctor_id = d.doctor_id;<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL Self Join Visual Diagram<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL Self Join Labs<\/h2>\n\n\n\n