SQL Full Join

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;

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;

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;

SELECT p.first_name, a.appointment_date

FROM Patients p

FULL JOIN Appointments a ON p.patient_id = a.patient_id;

SQL Full Join Visual Diagram

SQL Full Join Labs