JIYIK CN >

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

Full outer join is used to merge or combine the entire data from two separate tables. For example, suppose we have two tables named student_idand student_namewhich have a common column.

We can completely merge these two tables in MySQL by matching the values ​​of the common columns and UNION, LEFT JOINand . Although the process may seem complicated at first glance, let's understand it step by step.RIGHT JOIN

Before we begin, we will student_detailscreate a dummy dataset by creating a 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 see 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 student_markstable called that contains stu_idthe marks for each student corresponding to . We can create 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 our goal be to perform a full outer join on the student_detailsand student_markstables, with stu_idas the common column in both tables.

FULL JOINStatements in MySQL

FULL OUTER JOINThe basic syntax of the 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 shown in the above query, our goal is to join two tables named and based on the common column with the help of 左连接and .右连接idtable_1table_2

We can use the following query to solve the problem student_detailswith the and student_markstables.

-- 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 shown in the above code, we are stu_idmerging the two tables under consideration based on the column. The output of the above code 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

It can be seen from the output block stu_marksthat after the full outer join , stu_idit is correctly assigned to each student according to .

So, with the help of the statement and the AND UNIONon two different tables , we can effectively create a full outer join in MySQL without any duplicate rows.左连接右连接