How To Use Full Outer Join In MySQL

Introduction

In Relational Database Management System(RDBMS), database tables frequently contain related relevant information in more than one table that needs to be merged or combined for various purposes like data analysis, reporting, decision-making, etc. To accomplish this, MySQL provides various join types, among which one type of join is full outer join. In this article, we will review the full outer join, look over its syntax, and use it at the end to merge two tables.

What is a Full Outer Join?

A full outer join, also known as the full join, combines the results generated by both the left and right outer joins. A full outer join returns all data from both tables and matches them where matching is possible; when the results are not matched between the two tables, those fields contain NULL values in the place of the missing data.

We mainly use a full outer join when we need to merge data from two tables, but we are not sure that all the data in each data table will have a matching value in the other table. It also comes in handy when we find missing data from one table in another.

Syntax

SELECT *
FROM table1_name
FULL OUTER JOIN table2_name
ON table1_name.primary_key_column = table2_name.foreign_key_column;

Table1_name and table2_name are two tables on which the join is being performed. In the above syntax, the SELECT * specifies that we must see all the columns available on both tables. The ON keyword specifies the join condition; the join condition can be any condition that compares the value in the columns we are joining. 

Example

Let us practice with the help of an example to understand the concept more. Suppose we have two tables, Students and Courses, which contain the following data.

Courses table

Course_ID	Course_Name
C1	Science
C2	Maths
C3	Sociology
C4	English Literature
C5	Commerce
C6	Computers

Students table

Student_ID	Student_Name	Course_ID
1	Aman	C1
2	Ankit	C2
3	Ashutosh	C1
4	Rohit	C3
5	Shravan	C2
6	Tanmay	C6

Suppose we want to combine the data of these two tables using full outer join to generate a response to see all the selected courses and by which student. To perform this, we need to write a query.

SELECT * 
FROM Students
FULL OUTER JOIN Courses 
ON Students.Course_ID = Couse.Course_ID;

The result of the query should be like this.

Student_ID	Student_Name	Course_ID	Course_Name
1	Aman	C1	Science
2	Ankit	C2	Maths
3	Ashutosh	C1	Science
4	Rohit	C3	Sociology
5	Shravan	C2	Maths
6	Tanmay	C6	Computers

Advantages of Full Outer Join

1. A full outer join combines data from both tables. A full outer join combines all data from both tables and returns all rows, including rows with no matching values. 
2. We can avoid data loss using a full outer join. Full outer join ensures that no data is lost during the merging process. This is useful when keeping all the data from both tables.
3. Full outer join finds the missing data as it does not eliminate the mismatched row; it just inserts a NULL value to all the empty columns of that particular mismatched row.
4. It allows the use of complex conditions in the join clause, giving greater control over the data.

Conclusion

We understand that full outer join is a powerful MySQL tool used to join two tables when the database creator wants to create all the data from both tables or find the missing data. Now we are ready to implement this learning in our projects. "ALL THE BEST!"