How to Full Join in MySQL

Preet Sanghavi Feb 02, 2024
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.

Note
We can avoid having the duplicate column stu_id by specifying the exact column names to be joined with the help of left join and right join. This would create an outer join with no duplicate rows because of our query’s UNION clause.

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.

Preet Sanghavi avatar Preet Sanghavi avatar

Preet writes his thoughts about programming in a simplified manner to help others learn better. With thorough research, his articles offer descriptive and easy to understand solutions.

LinkedIn GitHub

Related Article - MySQL Query