MySQL FULL JOIN [Explained With Easy Examples]

Full Join

In this tutorial, we will study the MySQL FULL JOIN (also known as FULL OUTER JOIN) keyword. A join is used to combine two or more tables using a commonly related column between them.

There are different types of joins in MySQL. They are –

In this article, we will focus on the full join, sometimes also referred to as the full outer join, and how to use the MySQL FULL JOIN keyword to make a full join.

Note: Some SQL systems may not use both the keywords – FULL JOIN or FULL OUTER JOIN. In that case, use the other keyword. Also note that MariaDB does not support the FULL JOIN/FULL OUTER JOIN keywords at all. But no worries, we will see towards the end of this post on how to make a full join in MariaDB.


What is a FULL JOIN?

The FULL JOIN or FULL OUTER JOIN keyword is used to select all records from the left table and right table. It combines both tables into a result-set and returns it to the user. Note that MySQL FULL JOIN is known to create large datasets. It works like the union operation you would have seen in set theory in mathematics. A full join is essentially a union of a left join with a right join.

Let us demonstrate the FULL JOIN using a diagram.

Full Join Diagram
Full Join Diagram

As you can glean from the diagram, all the records from both the two tables are selected. If table1 does not have the common values as table2 then those records are populated with NULL or are kept blank depending on which SQL system you are using.


Syntax of MySQL FULL JOIN

SELECT expression FROM table1 [t1] 
FULL JOIN table2 [t2] 
ON table1.column_name = table2.column_name;

Here ‘t1’ and ‘t2’ are optional aliases that you can have for the table names. We will see more of it in the examples. The columns mentioned after the ON keyword are the related common columns using which the full join is to be performed. Note that if you want to use FULL OUTER JOIN then just replace the FULL JOIN keyword in the above syntax with the FULL OUTER JOIN keyword.


Examples of MySQL FULL JOIN/ FULL OUTER JOIN

Consider the below Students table and Marks table.

Fulljoin Students Table
Students Table
Outer Join Marks Table
Marks Table

The Students table stores the details of the student in every row. The Marks table stores every student’s marks in English, Maths and Science out of 100.

Simple Example of the MySQL FULL JOIN/FULL OUTER JOIN

Let us now make a full join on the Students table and Marks table such that we see all the records from both tables.

The first step is to find the commonly related columns between the two tables. In this case, they are ID from the Students table and StudentID from the Marks table. Let us now write a query for the full join using the SELECT statement.

SELECT * FROM Students s FULL OUTER JOIN Marks m ON s.Id=m.StudentID;

And the output is –

MySQL Full Join FULL Outer Join 1

We used the FULL OUTER JOIN keyword earlier. If you want to use the FULL JOIN keyword then the query is –

SELECT * FROM Students s FULL JOIN Marks m ON s.Id=m.StudentID;

And the output remains the same as seen below.

MySQL Full join Full Outer Join 2

Swapping Table Sides

What do you think will happen if we swap the sides of the Students table and the Marks table? Like what if we made the Marks table as the left side table and the Students table as the right side table? The output does change! Let us see an example of this.

SELECT * FROM Marks m FULL JOIN Students s ON m.StudentID=s.Id;

And the output is –

Full Outer Join 3

FULL JOIN With the WHERE clause

We can also use different clauses like the WHERE clause with the MySQL FULL JOIN/FULL OUTER JOIN keyword. Let us display a full join where the student ID from the Student table is greater than 5. The query is – 

SELECT * FROM Students s FULL JOIN Marks m ON s.Id=m.StudentID WHERE s.Id>5;

And the output is –

Full Outer Join 4

The entries from the Marks table are omitted because we are filtering through the ID column in the Students table. Now, let us see an example where the student ID from the Marks table is greater than . The query is – 

SELECT * FROM Students s FULL JOIN Marks m ON s.Id=m.StudentID WHERE m.StudentID>5;

And the output is –

Full Outer Join 5

FULL JOIN in MariaDB

Like I mentioned earlier, MariaDB does not support the FULL JOIN or the FULL OUTER JOIN keywords. Strange, right? But what if you are using MariaDB and you want to make a full join. How do you do that? Well, the answer lies in the “What is a FULL JOIN?” section. Over there, I mentioned, A full join is essentially a union of a left join with a right join. What if we do a left join and a right join and then use the UNION keyword in between them? According to that statement, if we do this operation, we should get a full join even in MariaDB. Let’s take a look. The query is – 

SELECT * FROM Students s LEFT OUTER JOIN Marks m ON s.ID=m.StudentID 
UNION 
SELECT * FROM Students s RIGHT OUTER JOIN Marks m ON s.ID=m.StudentID;

And the output on MariaDB is –

Full Outer Join 6

Yay! We could make a full join in MariaDB by using the left join and right join combined together with the UNION keyword.


Conclusion

FULL JOIN is an extremely important operation when it comes to MySQL. Joining two tables and displaying that data provides useful insights into the data. I would highly recommend you to practice some examples on FULL JOIN.