FULL JOIN

FULL JOIN (also known as FULL OUTER JOIN) is used to return all records from both the left and right tables.

When there is a match between records in both tables, it combines them. If no match is found on one side, the missing side will show NULL.

Syntax:

SELECT table1.column_name, table2.column_name
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Note: MySQL does not support FULL OUTER JOIN directly. But you can achieve it using UNION of LEFT JOIN and RIGHT JOIN.

Example (Using UNION):

SELECT students.student_id, students.student_name, fees.total_fees
FROM students
LEFT JOIN fees
ON students.student_id = fees.student_id

UNION

SELECT students.student_id, students.student_name, fees.total_fees
FROM students
RIGHT JOIN fees
ON students.student_id = fees.student_id;

Query

SELECT 
    students.student_id,
    students.student_name,
    fees.total_fees,
    fees.paid_fees,
    fees.status
FROM students
LEFT JOIN fees ON students.student_id = fees.student_id

UNION

SELECT 
    students.student_id,
    students.student_name,
    fees.total_fees,
    fees.paid_fees,
    fees.status
FROM students
RIGHT JOIN fees ON students.student_id = fees.student_id;

Out Put