MySQL FULL JOIN [Explained With Easy Examples]

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.

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

We do not have a full join or full outer join in MySQL. But instead, we will emulate them using a combination of LEFT and RIGHT JOINS and the UNION query.

With two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

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

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
LEFT JOIN Marks m ON s.Id = m.StudentID
UNION
SELECT *
FROM Students s
RIGHT JOIN Marks m ON s.Id = m.StudentID
WHERE s.Id IS NULL;
Code language: SQL (Structured Query Language) (sql)

And the output is –

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 
LEFT JOIN Students s 
ON m.StudentID = s.Id
UNION
SELECT *
FROM Marks m 
RIGHT JOIN Students s 
ON m.StudentID = s.Id
WHERE m.StudentID IS NULL;
Code language: SQL (Structured Query Language) (sql)

And the output is –

FULL JOIN With the WHERE clause

We can also use different clauses like the WHERE clause with the MySQL LEFT JOIN and RIGHT JOIN keywords. 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
LEFT JOIN Marks m ON s.Id = m.StudentID
WHERE s.Id > 5

UNION

SELECT *
FROM Students s
RIGHT JOIN Marks m ON s.Id = m.StudentID
WHERE s.Id > 5 AND s.Id IS NULL;
Code language: SQL (Structured Query Language) (sql)

And the output is –

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 5. The query is –

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

UNION ALL

SELECT *
FROM Students s
RIGHT JOIN Marks m
ON s.Id = m.StudentID
WHERE s.Id IS NULL AND m.StudentID > 5;
Code language: SQL (Structured Query Language) (sql)

And the output is –

FULL JOIN in MariaDB

As 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;Code language: SQL (Structured Query Language) (sql)

And the output on MariaDB is –

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.