阅读(1.9k) 书签赞(0) 我要纠错

OceanBase 连接

2021-06-28 10:17 更新

连接（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)
+---------------------------------------------------------+

以上内容是否对您有帮助：

← OceanBase 集合

OceanBase 子查询 →

写笔记

我要补充