OceanBase Cloud快速入门第63期：如何使用FULL JOIN?

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/

全联接（FULL JOIN）表示全外联接，使用该语句可以返回所有联接中的所有行，不论他们是否匹配。

背景信息

外联接（OUTTER JOIN）是使用比较运算符对两个表中的数据进行比较，联接结果不仅包含符合联接条件的行，同时也包含不符合条件的行。

外联接包括全联接（FULL JOIN）、左联接（LEFT JOIN）和右联接（RIGHT JOIN）。外联接返回满足联接条件的所有行，同时从一个表返回没有使用的行，在另一个表相应位置填充 NULL。

本文主要提供全联接的示例指导，左联接相关示例请参见 左联接，右联接相关示例请参见 右联接。

FULL JOIN 语法

外联接语句中一般包括左表和右表，出现在 JOIN 子句最左边的为左表，出现在 JOIN 子句最右边的为右表。

FULL JOIN 的语法格式如下：

SELECT select_list FROM table_name1 FULL JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ];

其中，table_name1 为左表，table_name2 为右表。

示例

创建示例表并插入适当数据。

obclient [info]> CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected

obclient [info]> CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected

obclient [info]> INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0

obclient [info]> INSERT INTO tbl_b VALUES(1,1001),(3,1003),(5,1005);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

简单全联接查询

使用全联接查询时，左表或者右表中的行找不匹配行时，系统都会自动填充 NULL。

示例：将表 tbl_a 和表 tbl_b 执行全联接查询，并获取返回结果。

obclient> SELECT * FROM tbl_a;
+------+------+
| id   | name |
+------+------+
|    1 | ab   |
|    2 | cd   |
|    3 | ef   |
|    4 | gh   |
+------+------+
4 rows in set

obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
|      1 |  1001 |
|      3 |  1003 |
|      5 |  1005 |
+--------+-------+
3 rows in set

obclient> SELECT * FROM tbl_a FULL JOIN tbl_b ON tbl_a.id=tbl_b.number;
+------+------+--------+-------+
| id   | name | number | value |
+------+------+--------+-------+
|    1 | ab   |      1 |  1001 |
|    2 | cd   |   NULL |  NULL |
|    3 | ef   |      3 |  1003 |
|    4 | gh   |   NULL |  NULL |
| NULL | NULL |      5 |  1005 |
+------+------+--------+-------+
5 rows in set

可以看到，表 tbl_a 中没有值为 5 的行，返回结果中其行记录被填充为 NULL，而 表 tbl_b 中没有值为 2、4 的行，返回结果中这 2 行被填充为NULL。

全联接和 WHERE 子句联合使用

您可以使用全联接获取联接结果，再使用 WHERE 子句对联接结果进行过滤。

示例 2：将表 tbl_a 和表 tbl_b 执行全联接查询后，返回 tbl_b 表中 value=1003 的数据。

obclient> SELECT * FROM tbl_a;
+------+------+
| id   | name |
+------+------+
|    1 | ab   |
|    2 | cd   |
|    3 | ef   |
|    4 | gh   |
+------+------+
4 rows in set

obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
|      1 |  1001 |
|      3 |  1003 |
|      5 |  1005 |
+--------+-------+
3 rows in set

obclient> SELECT * FROM tbl_a FULL JOIN tbl_b ON tbl_a.id=tbl_b.number WHERE tbl_b.value=1003;
+------+------+--------+-------+
| id   | name | number | value |
+------+------+--------+-------+
|    3 | ef   |      3 |  1003 |
+------+------+--------+-------+
1 row in set

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/