SQL FULL OUTER JOIN

Posted on Wednesday, 08 March 2023 10:21 AM

A Full Join is a type of SQL join that combines all the rows from two or more tables, including the rows that do not have matching values in the join condition. It returns a result set that contains all the rows from both tables, matching rows where possible, and filling in values where there is no match.

FULL JOIN Syntax

SELECT column1, column2, …
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

For example,

SELECT student.name, subject.name, exam.marks
FROM student
FULL OUTER JOIN exam ON student.id = exam.student_id
FULL OUTER JOIN subject ON exam.subject_id = subject.id;

OUTPUT

In this example,

We use a FULL OUTER JOIN to combine all the rows from the student, exam, and subject tables, matching rows where possible, and filling in values where there is no match.

Here, the join condition is defined in the ON clause, where we match the student id with the exam student id and exam subject id with the subject id.

The result of this query will be a table that contains all rows from the student table, all rows from the exam table, and all rows from the subject table, with values in the columns where there is no match.

For example, if a student didn't take any exams, the exam columns will contain values for that student. Similarly, if a subject has no exams, the exam columns for that subject will contain values.

It is important to note that not all database management systems support FULL JOIN. In cases where FULL JOIN is not supported, you can achieve a similar result by using a combination of LEFT and RIGHT JOINs.

K-Learn