MariaDB Full Outer Join - How to implement

In this MariaDB tutorial, we will learn about MariaDB Full Outer Join. Additionally, we will cover the following topics.

What is Full Outer Join
Full Outer Join in MariaDB
MariaDB Full Outer Join Using Union All

Table of Contents

What is Full Outer Join in Database

Here, we will learn about the basics of Full Outer Join in any database.

Full Outer Join is a clause of the SELECT statement which returns a set that contains rows from both the right and the left table.

In this, the resultant table obtained after the execution of SELECT along with Full Outer Join contains

All records from both the table.
Also includes records that do not match.
Rows with no match will have NULL column values.

When there are no matching rows for the row in the left table, all the values in the column of the right table will be NULL.

Likewise, when there are no matching rows for the row in the right table, all the values in the column of the left table will be NULL.

Note – MariaDB does not support the Full Outer Join or Full Join.

Full Outer Join in MariaDB

Here, we will learn about Full Outer Join in MariaDB and how to implement it in MariaDB.

As we know, Full Outer Join is not supported in MariaDB. So, we need an alternative to implement Full Outer Join in MariaDB.

This can be resolved by using Right Outer Join and Left Outer Join with Union in MariaDB.

We can right two queries each for Left Outer Join and Right Outer Join and merge them using Union in MariaDB.

We will take two tables i.e. USA_College and College_Course and implement Left Outer Join, Right Outer Join, and then Full Outer Join in MariaDB.

Read How to Drop Column from MariaDB Table

Left outer join in MariaDB

Let us see, how to use the Left Outer Join in MariaDB.

By using the left outer join in MariaDB, we can combine tables by using a given condition. It will return records consisting of the rows which satisfy the condition as well as the rows from the left table.

The syntax for Left Outer Join in MariaDB is given below.

SELECT *
FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;

Here,

table1 & table2 – Tables that is going to be merged.
column_name – Name of columns from respective tables.

Visual Implementation of Left Outer Join is given below.

To implement the Left Outer Join in MariaDB, type the following command.

SELECT *
FROM USA_College LEFT JOIN College_Course 
ON USA_College.College_Id = College_Course.Course_id;

Here, we are merging two tables i.e. USA_College and College_Course using Left Outer Join in MariaDB under the condition that College_Id of USA_College is equal to Course_Id of College_Course.

Read How to Create Trigger in MariaDB

Right outer join in MariaDB

Let us see, how to use the Right Outer Join in MariaDB.

We can use the right outer join to combine multiple tables in MariaDB using a given condition. It will return a list of records that consist of the rows which satisfy the condition as well as the rows from the right table.

The syntax for Right Outer Join in MariaDB is given below.

SELECT *
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Here,

table1 & table2 – Tables that is going to be merged.
column_name – Name of columns from respective tables.

Visual Implementation of Right Outer Join is given below.

MariaDB Right Outer Join tutorial — MariaDB Right Outer Join

To implement the Right Outer Join in MariaDB, type the following command.

SELECT *
FROM USA_College RIGHT JOIN College_Course 
ON USA_College.College_Id = College_Course.Course_Id;

Here, we are merging two tables i.e. USA_College and College_Course using Right Outer Join in MariaDB under the condition that College_Id of USA_College is equal to Course_Id of College_Course.

After seeing the implementation of both Left Outer Join and Right Outer Join, we are going to implement the Full Outer Join in MariaDB.

The syntax for Full Outer Join in MariaDB is given below.

SELECT *
FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;
UNION
SELECT *
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Here,

table1 & table2 – Tables that is going to be merged.
column_name – Name of columns from respective tables.

Visual Implementation of Right Outer Join is given below.

To implement the Full Outer Join in MariaDB, type the following command.

SELECT *
FROM USA_College LEFT JOIN College_Course 
ON USA_College.College_Id = College_Course.Course_id
UNION
SELECT *
FROM USA_College RIGHT JOIN College_Course 
ON USA_College.College_Id = College_Course.Course_id;

Here, we are merging two tables i.e. USA_College and College_Course using Right Outer Join and Left Outer Join in MariaDB under the condition that College_Id of USA_College is equal to Course_Id of College_Course.

Union – Used to combine multiple SELECT statements into a single resulting table.

Note – MariaDB Union removes all the duplicate rows from the resultant set.

This is how you can implement Full Outer Join in MariaDB using Left and Right Outer Join with Union.

Read How to Add Column in MariaDB

MariaDB Full Outer Join With UNION ALL

Here, we will learn about the implementation of Full Outer Join using Union All.

The major difference between Union and Union All

Union removes duplicate rows
Union All does not remove duplicate rows

In the implementation of Full Outer Join using Union, the resultant set does not include duplicate rows which is not the ideal behavior of full outer join.

To implement proper working of the full outer join, we need to use Union All to merge the left outer join and the right outer join.

The syntax of Union All to merge the left outer join and the right outer join is given below.

SELECT *
FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;
UNION ALL
SELECT *
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Here,

table1 & table2 – Tables that is going to be merged.
column_name – Name of columns from respective tables.

We will take two tables i.e. Cricket_Lover and Football_Lover to implement Full Outer Join using Union All in MariaDB.

MariaDB Full Outer Join With UNION ALL — Cricket_Lover MariaDB table

To implement Full Outer Join, we need to type the following command.

SELECT *
FROM Cricket_Lover LEFT JOIN Football_Lover 
ON Cricket_Lover.id = Football_Lover.id
UNION ALL
SELECT *
FROM Cricket_Lover RIGHT JOIN Football_Lover 
ON Cricket_Lover.id = Football_Lover.id;

Here, we are merging two tables i.e. Cricket_Lover and Football_Lover using Right Outer Join and Left Outer Join in MariaDB under the condition that the id of Cricket_Lover is equal to the id of Football_Lover.

Implementation of full outer join on the same tables using union will show the difference in the result set.

SELECT *
FROM Cricket_Lover LEFT JOIN Football_Lover 
ON Cricket_Lover.id = Football_Lover.id
UNION ALL
SELECT *
FROM Cricket_Lover RIGHT JOIN Football_Lover 
ON Cricket_Lover.id = Football_Lover.id;

We can easily see the difference between the result set of both the implementation. So, the ideal working of full outer join can be implemented using left outer join and right outer join using union all.