A Full Join or Full Outer Join returns all records when there is a match in either left or right table records. If there is no match, the result is NULL on the side that does not have match.
SQL Full Join Syntax
SELECT column1, column2, ... FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
SQL Full Join Doctors and Appointments Example
This query returns all doctors and their corresponding appointments, including those doctors without appointments and appointments without corresponding doctors.
SELECT d.first_name, d.last_name, a.appointment_date FROM Doctors d FULL JOIN Appointments a ON d.doctor_id = a.doctor_id;
SQL Full Join Patients and Appointments Example
This query retrieves all patients along with their appointments. If a patients does not have an appointment, the appointment date will show as NULL.
SELECT p.first_name, a.appointment_date FROM Patients p FULL JOIN Appointments a ON p.patient_id = a.patient_id;