How to use the FULL OUTER JOIN function in MySQL to obtain the union of two tables

How to use the FULL OUTER JOIN function in MySQL to obtain the union of two tables

In MySQL, the FULL OUTER JOIN function is a powerful connection operation that combines inner joins and outer joins. It can be used to get the union of two tables, that is, combine all the data in the two tables into a single result set. This article will introduce the usage of the FULL OUTER JOIN function and provide some sample code to help readers better understand.

The syntax of the FULL OUTER JOIN function is as follows:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

In this syntax, table1 and table2 are the two tables to be connected, column is the connection condition, and * means to select all columns.

Suppose we have two tables: Table A and Table B. Their structure and data are as follows:

Table A:

+----+--------+
| id | name   |
+----+--------+
| 1  | Tom    |
| 2  | Jerry  |
| 3  | Alice  |
+----+--------+

Table B:

+----+--------+
| id | name   |
+----+--------+
| 1  | Peter  |
| 2  | Jerry  |
| 4  | Bob    |
+----+--------+

Now we want to get the union of table A and table B.

The sample code using the FULL OUTER JOIN function is as follows:

SELECT *
FROM tableA
FULL OUTER JOIN tableB
ON tableA.id = tableB.id;

After executing the above code, we will get the following results:

+------+---------+---------+
| id   | name    | name    |
+------+---------+---------+
| 1    | Tom     | Peter   |
| 2    | Jerry   | Jerry   |
| 3    | Alice   | NULL    |
| NULL | NULL    | Bob     |
+------+---------+---------+

As can be seen from the above results, FULL The OUTER JOIN function includes all data from Table A and Table B. It merges rows with the same value in the two tables based on the join condition, and if there is no matching row in a table, fills the corresponding column with NULL.

In the above example, the rows with id 1 and 2 are present in both tables, so they are merged into one row. The row with id 3 only exists in table A, and the row with id 4 only exists in table B, so they are displayed as a separate row.

In addition to SELECT *, we can also selectively specify the required columns, as shown below:

SELECT tableA.id, tableA.name, tableB.name
FROM tableA
FULL OUTER JOIN tableB
ON tableA.id = tableB.id;

After executing the above code, we will get the following results:

+------+---------+---------+
| id   | name    | name    |
+------+---------+---------+
| 1    | Tom     | Peter   |
| 2    | Jerry   | Jerry   |
| 3    | Alice   | NULL    |
| NULL | NULL    | Bob     |
+------+---------+---------+

From the above example, we can see how to use the FULL OUTER JOIN function to obtain the union of two tables. It can help us merge the data in the two tables together, making data processing more convenient.

To summarize, the FULL OUTER JOIN function is a powerful connection operation in MySQL for obtaining the union of two tables. It can merge all the data from two tables into one result set and merge the rows with the same value based on the join condition. Through the introduction and sample code of this article, I hope it can help readers better understand the usage and usage skills of the FULL OUTER JOIN function.

The above is the detailed content of How to use the FULL OUTER JOIN function in MySQL to obtain the union of two tables. For more information, please follow other related articles on the PHP Chinese website!