How to Full Join in MySQL

This tutorial aims to explore how to perform a full join or a full outer join in MySQL.

A full outer join is used to merge or combine the entire data from two separate tables. For example, consider that we have two tables named student_id and student_name with a common column.

We can merge these two tables entirely in MySQL by matching the values of the common column along with a UNION, LEFT JOIN, and RIGHT JOIN. While this process seems complicated at first glance, let us understand these in steps.

Before we begin, we will create a dummy dataset by creating a student_details table with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

To view the entries in the data, we use the following code.

SELECT * FROM student_details;

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

We need another table named student_marks containing the marks of each student corresponding to the stu_id. We can make such a table using the following query.

-- create the table student_marks
CREATE TABLE student_marks(
  stu_id int,
  stu_marks int
);
-- insert rows to the table student_marks
INSERT INTO student_marks(stu_id,stu_marks) 
 VALUES(1,10),
 (2,20),
 (3,30),
 (4,7),
 (5,9),
 (6,35),
 (7,15);

We can visualize this table with the help of the following query.

SELECT * from student_marks;

Output:

stu_id  stu_marks
1		10
2		20
3		30
4		7
5		9
6		35
7		15

Let us aim at making a full outer join on the student_details and student_marks table with stu_id as the common column in both the tables.

FULL JOIN Statement in MySQL

The basic syntax of the FULL OUTER JOIN technique is as follows.

SELECT * FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
UNION
SELECT * FROM table_1
RIGHT JOIN table_2 ON table_1.id = table_2.id

As seen in the query above, we aim to join the two tables named table_1 and table_2 based on the common id column with the help of a left join and a right join.

We can use the following query to solve our problem with the student_details and the student_marks tables.

-- Full Join using UNION
SELECT * FROM student_details
LEFT JOIN student_marks ON student_details.stu_id = student_marks.stu_id
UNION
SELECT * FROM student_details
RIGHT JOIN student_marks ON student_details.stu_id = student_marks.stu_id

As seen in the code above, we are merging both the tables under consideration based on the stu_id column. The output of the code above is as follows.

stu_id	stu_firstName	stu_lastName  stu_id	stu_marks
1		Preet			Sanghavi		1		10
2		Rich			John			2		20
3		Veron			Brow			3		30
4		Geo				Jos				4		7
5		Hash			Shah			5		9
6		Sachin			Parker			6		35
7		David			Miller			7		15

As seen in the output block, stu_marks are correctly assigned to each student based on the stu_id after the full outer join.

Therefore, with the help of the UNION statement along with a left join and a right join on two different tables, we can efficiently create a full outer join in MySQL without any duplicate rows.