OceanBase 连接

连接（Join）是将两个或多个表、视图或物化视图的结合在一起的查询。当查询的 FROM 子句中出现多个表时 OceanBase 数据库会进行连接查询，查询输出列可以从 FROM 子句任何表中选取。如果多个表都有一个列名，那么您必须用表名限定查询过程中对这些列的所有引用。数据库中的连接类型一般包括 inner join、outer join、semi-join 和 anti-join。其中 Semi-join 和 Anti-join 都是通过子查询改写得到，OceanBase 本身并没有表述 Anti-join 和 Semi-join 的语法。

连接条件

连接条件是将多表结合在一起的规则，存在于从句或 WHERE 子句中，用于比较来自不同的表两列，多数连接至少包含一个连接条件。连接条件可以分为等值连接（比如 t1.a = t2.b）和非等值连接（t1.a < t2.b），相比于非等值连接条件, 等值连接条件允许数据库使用高效的连接算法，比如 Hash Join 和 Merge-Sort join。

为了执行连接，OceanBase 从不同表取行组合成对，使用连接条件进行匹配。要执行两表以上的多表连接，OceanBase 首先根据比较它们的列的连接条件连接其中的两个表，然后根据包含连接表和新表列的连接条件将结果连接到另一个表。优化器根据连接条件、基表索引以及可用统计信息确定 OceanBase 连接顺序。

WHERE 子句除了连接条件还可能含有其他条件，这些仅引用一个表的条件可以进一步限制连接查询返回的行数。

等值连接（EQUI-JOINS）

等值连接（Equijoins）是连接条件包含等式运算符的连接。进行等值连接时将特定列满足等值条件的行进行组合输出。

自连接（SELF-JOIN）

自连接是表与其自身的连接。该表在 FROM 子句中出现两次，后跟表别名，这些别名限定联接条件中的列名。执行自连接时OceanBase数据库将组合并返回满足连接条件的行。

笛卡儿积（Cartesian Products）

如果连接查询中的两个表没有连接条件，OceanBase 数据库返回其笛卡尔乘积，使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积总是生成许多行，很少有用。例如，两个都有 100 行的表的笛卡尔积有 10,000 行。除非您特别需要笛卡尔乘积，否则始终包括一个连接条件。如果查询连接了三个或多个表，并且没有为特定对指定连接条件，则优化器可以选择避免生成中间笛卡尔乘积的连接顺序。

内连接（INNER JOIN）

内连接（INNER JOIN）是数据库中最基本的连接操作。内连接基于连接条件将两张表（如 A 和 B）的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接条件的组合。当连接条件被满足，A 和 B 中匹配的行会按列组合（并排组合）成结果集中的一行。连接产生的结果集等于首先对两张表做笛卡尔积，将 A 中的每一行和 B 中的每一行组合，然后返回满足连接条件的记录。

外连接（OUTER JOIN）

外连接（OUTER JOIN）返回满足连接条件的所有行，同时从一个表返回没有使用的行，在另一个表相应位置填充 NULL。外连接可依据连接表保留左表、右表或全部表的行而进一步分为左连接、右连接和全连接。其中左连接（LEFT [OUTER] JOIN）中左表行未在右表匹配到行时，在右表自动填充 NULL。右连接（RIGHT [OUTER] JOIN）中右表行未在左表匹配到行时，在左表自动填充 NULL。全连接（FULL [OUTER] JOIN）左表或者右表未在其它表匹配到行时均会填充 NULL。

SEMI 连接（SEMI-JOIN）

当 A 表和 B 表进行 LEFT 或 RIGHT ANTI-JOIN 的时候，它只返回 A 或 B 中所有能够在 B 或 A 中找到匹配的行。SEMI-JOIN 只能通过子查询展开得到。

ANTI 连接（ANTI-JOIN）

当 A 表和 B 表进行 LEFT 或 RIGHT ANTI-JOIN 的时候，它只返回 A 或 B 中所有不能在 B 或 A 中找到匹配的行。类似于 SEMI-JOIN，ANTI-JOIN 也只能通过子查询展开得到。

示例

建立表 table_a 和表 table_b，并插入数据。执行以下语句：

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'福克斯');
INSERT INTO table_a VALUES(2,'警察');  
INSERT INTO table_a VALUES(3,'的士');  
INSERT INTO table_a VALUES(4,'林肯');  
INSERT INTO table_a VALUES(5,'亚利桑那州');  
INSERT INTO table_a VALUES(6,'华盛顿');  
INSERT INTO table_a VALUES(7,'戴尔');  
INSERT INTO table_a VALUES(10,'朗讯'); 
CREATE TABLE table_b(PK INT, name VARCHAR(25));
INSERT INTO table_b VALUES(1,'福克斯');
INSERT INTO table_b VALUES(2,'警察');  
INSERT INTO table_b VALUES(3,'的士');  
INSERT INTO table_b VALUES(6,'华盛顿');  
INSERT INTO table_b VALUES(7,'戴尔');  
INSERT INTO table_b VALUES(8,'微软');  
INSERT INTO table_b VALUES(9,'苹果'); 
INSERT INTO table_b VALUES(11,'苏格兰威士忌');

自连接查询（SELF-JOIN）：

SELECT * FROM table_a ta, table_a tb WHERE ta.NAME = tb.NAME;

查询结果如下：

+------+-----------------+------+-----------------+
| PK   | NAME            | PK   | NAME            |
+------+-----------------+------+-----------------+
|    1 | 福克斯          |    1 | 福克斯          |
|    2 | 警察            |    2 | 警察            |
|    3 | 的士            |    3 | 的士            |
|    4 | 林肯            |    4 | 林肯            |
|    5 | 亚利桑那州      |    5 | 亚利桑那州      |
|    6 | 华盛顿          |    6 | 华盛顿          |
|    7 | 戴尔            |    7 | 戴尔            |
|   10 | 朗讯            |   10 | 朗讯            |
+------+-----------------+------+-----------------+

内连接（INNER JOIN）查询：

SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value 
FROM table_a A INNER JOIN table_b B ON A.PK = B.PK;

查询结果如下：

+------+-----------+------+-----------+
| A_PK | A_VALUE   | B_PK | B_VALUE   |
+------+-----------+------+-----------+
|    1 | 福克斯    |    1 | 福克斯    |
|    2 | 警察      |    2 | 警察      |
|    3 | 的士      |    3 | 的士      |
|    6 | 华盛顿    |    6 | 华盛顿    |
|    7 | 戴尔      |    7 | 戴尔      |
+------+-----------+------+-----------+

左连接查询：

SELECT  A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value 
FROM table_a A LEFT JOIN  table_b B ON A.PK = B.PK;

查询结果如下：

+------+-----------------+------+-----------+
| A_PK | A_VALUE         | B_PK | B_VALUE   |
+------+-----------------+------+-----------+
|    1 | 福克斯          |    1 | 福克斯    |
|    2 | 警察            |    2 | 警察      |
|    3 | 的士            |    3 | 的士      |
|    6 | 华盛顿          |    6 | 华盛顿    |
|    7 | 戴尔            |    7 | 戴尔      |
|    4 | 林肯            | NULL | NULL      |
|    5 | 亚利桑那州      | NULL | NULL      |
|   10 | 朗讯            | NULL | NULL      |
+------+-----------------+------+-----------+

右连接查询：

obclient> SELECT  A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value FROM table_a A RIGHT JOIN   table_b B ON A.PK = B.PK;

查询结果如下：

+------+-----------+------+--------------------+
| A_PK | A_VALUE   | B_PK | B_VALUE            |
+------+-----------+------+--------------------+
|    1 | 福克斯    |    1 | 福克斯             |
|    2 | 警察      |    2 | 警察               |
|    3 | 的士      |    3 | 的士               |
|    6 | 华盛顿    |    6 | 华盛顿             |
|    7 | 戴尔      |    7 | 戴尔               |
| NULL | NULL      |    8 | 微软               |
| NULL | NULL      |    9 | 苹果               |
| NULL | NULL      |   11 | 苏格兰威士忌       |
+------+-----------+------+--------------------+

全连接查询：

obclient> SELECT  A.PK AS A_PK,A.name AS A_Value,B.PK AS B_PK,B.name AS B_Value FROM table_a A FULL  JOIN table_b B ON A.PK = B.PK;

查询结果如下：

+------+-----------------+------+--------------------+
| A_PK | A_VALUE         | B_PK | B_VALUE            |
+------+-----------------+------+--------------------+
|    1 | 福克斯          |    1 | 福克斯             |
|    2 | 警察            |    2 | 警察               |
|    3 | 的士            |    3 | 的士               |
|    6 | 华盛顿          |    6 | 华盛顿             |
|    7 | 戴尔            |    7 | 戴尔               |
| NULL | NULL            |    8 | 微软               |
| NULL | NULL            |    9 | 苹果               |
| NULL | NULL            |   11 | 苏格兰威士忌       |
|    4 | 林肯            | NULL | NULL               |
|    5 | 亚利桑那州      | NULL | NULL               |
|   10 | 朗讯            | NULL | NULL               |
+------+-----------------+------+--------------------+

Semi 连接（semi-join）：有依赖关系的子查询被展开改写成 SEMI-JOIN。

explain SELECT * FROM table_a t1 WHERE t1.PK IN (SELECT t2.PK FROM table_b t2 WHERE t2.NAME = t1.NAME);

查询结果如下：

+------------------------------------------------+
| Query Plan                                     |
+------------------------------------------------+
=======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |HASH SEMI JOIN|    |8        |114 |
|1 | TABLE SCAN   |T1  |8        |38  |
|2 | TABLE SCAN   |T2  |8        |38  |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([T1.PK], [T1.NAME]), filter(nil),
      equal_conds([T1.PK = T2.PK], [T2.NAME = T1.NAME]), other_conds(nil)
  1 - output([T1.NAME], [T1.PK]), filter(nil),
      access([T1.NAME], [T1.PK]), partitions(p0)
  2 - output([T2.NAME], [T2.PK]), filter(nil),
      access([T2.NAME], [T2.PK]), partitions(p0)
+------------------------------------------------+

Anti 连接（anti-join）：有依赖关系的子查询被改写成 Anti-join。

EXPLAIN SELECT * FROM table_a t1 WHERE t1.PK NOT IN (SELECT t2.PK 
FROM table_b t2 WHERE t2.name = t1.name);

查询结果如下：

+------------------------------------------------+
| Query Plan                                     |
+------------------------------------------------+
=======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |HASH ANTI JOIN|    |0        |112 |
|1 | TABLE SCAN   |T1  |8        |38  |
|2 | TABLE SCAN   |T2  |8        |38  |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([T1.PK], [T1.NAME]), filter(nil),
      equal_conds([T2.NAME = T1.NAME]), other_conds([(T_OP_OR, T1.PK = T2.PK, 
      (T_OP_IS, T1.PK, NULL, 0), (T_OP_IS, T2.PK, NULL, 0))])
  1 - output([T1.NAME], [T1.PK]), filter(nil),
      access([T1.NAME], [T1.PK]), partitions(p0)
  2 - output([T2.NAME], [T2.PK]), filter(nil),
      access([T2.NAME], [T2.PK]), partitions(p0)
+---------------------------------------------------------+

w3cschool 编程狮，随时随地学编程

OceanBase 连接

连接条件

等值连接（EQUI-JOINS）

自连接（SELF-JOIN）

笛卡儿积（Cartesian Products）

内连接（INNER JOIN）

外连接（OUTER JOIN）

SEMI 连接（SEMI-JOIN）

ANTI 连接（ANTI-JOIN）

示例

OceanBase 产品简介

OceanBase 快速入门

OceanBase 教程

OceanBase 控制台指南

OceanBase 集群工作台

OceanBase 租户

OceanBase 监控

OceanBase 诊断

OceanBase 备份恢复管理

OceanBase 安全设置

OceanBase 参数管理

OceanBase 数据传输

OceanBase 产品简介

OceanBase 产品架构

OceanBase 产品功能

OceanBase 快速入门

OceanBase 迁移项目管理

OceanBase MySQL数据库迁移

OceanBase Oracle数据库迁移

OceanBase 常见问题

OceanBase ODC 使用指南

OceanBase Web 版 ODC

OceanBase 连接数据库

OceanBase 使用工作台

OceanBase 使用工具

OceanBase 数据导出和导入

OceanBase 任务管理

OceanBase 数据库对象

OceanBase 表对象

OceanBase 视图对象

OceanBase 函数对象

OceanBase 存储过程对象

OceanBase 序列对象

OceanBase 程序包对象

OceanBase 触发器对象

OceanBase 类型对象

OceanBase 同义词对象

OceanBase 客户端版 ODC

OceanBase 连接数据库

OceanBase 使用工作台

OceanBase 使用工具

OceanBase 数据导出和导入

OceanBase 任务管理

OceanBase 数据库对象

OceanBase 表对象

OceanBase 视图对象

OceanBase 函数对象

OceanBase 存储过程对象

OceanBase 序列对象

OceanBase 程序包对象

OceanBase 触发器对象

OceanBase 类型对象

OceanBase 同义词对象

OceanBase Connector/J 开发者指南

OceanBase 什么是OceanBase Connector/J

OceanBase Connector/J 使用指南

OceanBase 基本操作

OceanBase Java数据流

OceanBase 使用LOB

OceanBase 数据源和URL

OceanBase 结果集

OceanBase 语句缓存

OceanBase 参考信息

OceanBase Oracle 模式特有功能

OceanBase 分布式事务

OceanBase SQL 参考（MySQL 模式）

OceanBase 基本元素

OceanBase 数据类型

OceanBase 运算符